Location of n-th highest value in the range

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.

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.

"Oscar" wrote:

> 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.

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:

> 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.
>
> "Oscar" wrote:
>
> > 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.

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

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.

"Oscar" wrote:

> 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:
>
> > 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.
> >
> > "Oscar" wrote:
> >
> > > 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.

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

Oscar.

"[email protected]" je napisal:

> 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
>
>

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:

> 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.