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

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

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.  Reply With Quote

2. Hi and welcome
And what does not work?
Please post a sample sheet ( no pics please) showing data, expected results and what goes wrong  Reply With Quote

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

{=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  Reply With Quote

5. 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.  Reply With Quote

6. Originally Posted by AliGW 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  Reply With Quote

7. 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.  Reply With Quote

8. ## Re: OFFSET Originally Posted by AliGW 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.

{=offset(ROUND(MAX(IF(\$D\$8:\$D\$396=S1,\$G\$8:\$G\$396)),2)0,-4)}
=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  Reply With Quote

9. Originally Posted by AliGW 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  Reply With Quote

10. 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!  Reply With Quote

#### Posting Permissions

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