Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 30

Thread: Why won't this work? I KNOW it's me...

  1. #1
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0

    Why won't this work? I KNOW it's me...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Formula:
    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2)0,-4)}

    Assuming the OFFSET arguments are:
    =OFFSET (reference, rows, cols, [height], [width])


    • reference - The starting point, supplied as a cell reference or range. My reference is the MAX IF
    • rows - The number of rows to offset below the starting reference. Rows=0
    • cols - The number of columns to offset to the right of the starting reference. Columns=-4
    • height - [optional] The height in rows of the returned reference.
    • width - [optional] The width in columns of the returned reference.
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,656
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    And what does not work?
    Please post a sample sheet ( no pics please) showing data, expected results and what goes wrong
    Thank you Ken for this secure forum.

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription
    Comma instead of bracket!

    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2,0,-4)}
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    [QUOTE=AliGW;33149]Comma instead of bracket!

    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2,0,-4)}

    Thanks Ali,
    But that statement is unbalanced. The right (close) parenthesis is to close the ROUND function. (2 digits).


    Thanks
    -Jim
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription
    OK, well move it, then. Either way, your original formula was missing a comma:

    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2)0,-4)} - should be a comma after the bracket.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    Quote Originally Posted by AliGW View Post
    OK, well move it, then. Either way, your original formula was missing a comma:

    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2)0,-4)} - should be a comma after the bracket.

    That...was my original post. I can tell by Excel...it will capitalize Functions when it works.

    -Jim
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription
    Sorry - don't understand your last post. Did you understand mine? What I am saying is that the formula will not work because there is a COMMA missing after the bracket I've highlighted in red. Have you tried adding the comma and entering the formula?

    Copy and paste this (enter as an array) and see if it's accepted:

    =OFFSET(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2),0,-4)

    What exactly are you trying to get Excel to do with this formula? Explain in words.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0

    Re: OFFSET

    Quote Originally Posted by AliGW View Post
    Sorry - don't understand your last post. Did you understand mine? What I am saying is that the formula will not work because there is a COMMA missing after the bracket I've highlighted in red. Have you tried adding the comma and entering the formula?

    Copy and paste this (enter as an array) and see if it's accepted:

    =OFFSET(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2),0,-4)

    What exactly are you trying to get Excel to do with this formula? Explain in words.
    I'll try this again...wrote a reply and it disappeared somehow. Will copy it first.


    OK. My original post the array was this:
    {=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2)0,-4)}
    Your last reply, the array was this:
    =OFFSET(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2),0,-4)
    Exactly the same without the C,S,E Braces. And your Offset is capitalized.


    What I am trying to accomplish is this:
    Return the maximum hours worked for a category. Return the amount AND the date.
    ROUND:
    Simply rounds the returned number to 2 digits as it is very long otherwise.
    These 3 statements work just fine as a stand alone array.
    Adding the OFFSET function is attempting to go 0 rows away and 4 cells (columns) left. Hence, the 0,-4
    Sounds great! Doesn't work.


    Thanks,
    -Jim
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  9. #9
    Acolyte Jym396's Avatar
    Join Date
    Jul 2017
    Location
    Monroeville, PA
    Posts
    23
    Articles
    0
    Quote Originally Posted by AliGW View Post
    Sorry - don't understand your last post. Did you understand mine? What I am saying is that the formula will not work because there is a COMMA missing after the bracket I've highlighted in red. Have you tried adding the comma and entering the formula?

    Copy and paste this (enter as an array) and see if it's accepted:

    =OFFSET(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2),0,-4)

    What exactly are you trying to get Excel to do with this formula? Explain in words.
    Waiting for Admin/Mod to approve my reply, I guess. Hey, they gotta do it. I understand.

    -Jim
    There are only 10 types of people in the world...those who understand binary..and those that don't.

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription
    Done it - I did not see until now that there were posts in moderation. We aren't sure why this happens in this random way - sorry it has disrupted the flow of this thread!
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •