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

Jym396

New member
Joined
Jul 24, 2017
Messages
23
Reaction score
0
Points
0
Location
Monroeville, PA
Formula:
{=offset(ROUND(MAX(IF($D$8:$D$396=S1,$G$8:$G$396)),2)0,-4)}

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

[/FONT]

  • 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.
 
Hi and welcome
And what does not work?
Please post a sample sheet ( no pics please) showing data, expected results and what goes wrong
 
Comma instead of bracket!

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

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
 
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
 
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!
 
OK - I've tidied the thread up now. Jim - can you please attach a sample workbook? The formula I last posted would still need to be CSE entered, by the way.
 
You didn't do anything! ;)

Yes, I did - I approved all the posts that had gone into moderation and then deleted all but one of them to tidy the thread (see post #8). :)

Now, will you please provide a sample workbook so that we can try to get your issue resolved?
 
I don't know how...:sorry: I have a snapshot of my sheet... but I don't think it will post?
 
Last edited:
No, not a snapshot. The workbook itself, but desensitized and cut down to just enough to demonstrate the issue. Use the Go Advanced button bottom right, and then you'll see the attachment icon on the editor toolbar.
 
No, not a snapshot. The workbook itself, but desensitized and cut down to just enough to demonstrate the issue. Use the Go Advanced button bottom right, and then you'll see the attachment icon on the editor toolbar.


OK...will try...scared! :scared:
 
Don't be - lots of people have managed it before. Think of it as a rite of passage ... :ranger:
 
OK - just having a look. Where do you want the results of the formula? Can you give me the answer you are expecting to appear in one of those cells, please?
 
Last edited:
RE:

OK - just having a look. Where do you want the results of the formula? Can you give me the answer you are expecting to appear in one of those cells, please?


Was looking to return it to R1 in the original one...but you can put it anywhere you want...just let me know. Now I can't get the simple MAX/IF to work in example. grrrr :mad:
 
Back
Top