Location of n-th highest value in the range

[Guest]

How can I get location of n-th highest (parameter) value in the range of
values. In case of equal values I want first occurance to be higher ranked.

Thanks, Oscar.

 

[Guest]

You may try the following:
=MATCH(LARGE(A1:A50,5),A1:A50)
This will find the 5th largest number in the A1:A50 range and return its
position. You may substitute the parameters to fit you case.

Hope this helps,
Miguel.

 

[Guest]

Since values are not set in ascending or descending order this doesn't work.
But even when if I rearange function to =MATCH(LARGE(A1:A50,5),A1:A50,0) it
still doesn't work as I would like it to. Suppose values in cells are:
A1: 6
A2: 4
A3: 4
A5: 3
A6: 5

I want 4 in cell A2 to be ranked as 3rd highest value and 4 in cell A3 as
4th higest.

Oscar.

"Miguel Zapico" je napisal:

 

[bplumhoff]

Hello Oscar,

Enter in cell B1:
=COUNTIF($A$1:$A$5,">" & A1) + COUNTIF($A$1:A1,A1)
and copy this down to B5.

Works with numbers and strings.

HTH,
Bernd

 

[Guest]

You are right, I was focusing on location, not in rank.
For ranking, I cannot think on a simple formula, as RANK gives the same rank
to similar numbers, but I can think on a workaround using an additional
column.
If you are using integers, or your know the precision of your numbers, you
may add a column beside the data with something like:
=A1 - ROW()*0.0001
The precision depends on the case, the idea is to have a list of different
numbers. Over that list you can use the RANK formula:
=RANK(B1,$B1:$B50)
And then hide the B column, so your original numbers will be side by side
with the ranking.

Surely there are better ways to achieve this, hope this one helps,
Miguel.

 

[Guest]

Thanks Bernd and Miguel. I already use solution similar to Bernd's, but would
prefer something with no additional column needed.

Oscar.

"(e-mail address removed)" je napisal:

 

[Guest]

This works:
{=MATCH(LARGE(A1:A50-ROW(A1:A50)/1000;n);A1:A50-ROW(A1:A50)/1000;0)}

Replace 1000 with high enough number so that row number / 1000 does not
efect ranking of "original values" in A1:A50.

Oscar

"Oscar" je napisal: