how to determine the position of the greatest value on an arbitrary range?

I have an excel sheet like this:

Desired result:

408 Max value 1st 1

432
432 Max value 5th 5
426
403
396
390




364
364
364 Max value 3rd 3
357
340


275
275 Max value 7th 7
253
230
230
221
221
221


I need to determine how many rows from the bottom of a range it takes to reach the maximum value. How can one do it on Excel? I’m a relative noob with it, can only do simple calculations – and this seems too complex for me.

Hi
Can you please attach it in excel?
See the yellow banner at the top of the page for more instructions.

Thanks.

ok. here's the spreadsheet that contains the problem.

You are counting from the bottom and up?

You can use something like that but the range needs to be adjusted manually:


=COUNTIF(A1,"<"&MAX(A1))+1

Hi
See attached:

Please Login or Register  to view this content.

Sorry I see now that it work only if the numbers are organized in descending order

fantastic. and yes, it's so complex that i could've never figured it out on my own... thank you, belinda200.

odd thing - it doesn't work on 2003, but does on 2016. reason to start using the later version!

Hi,
not so strange, MS adds new functions and developments with each new version.
yes, the formula seems complex/cumbersome, I assume there might be a simpler-looking solution but that's what I have come up with : )

You're welcome.

One more amendment!
I saw it still doesn't work in certain cases where the same MAX number repeats itself in none-consecutive cells

So use this:

Please Login or Register  to view this content.

super thanks. this works.

Great. You're welcome.