Select value from a range given a rank
 

I need to get a value out of a range when I know the rank.

I would like a symmetric function to RANK(value, range) which returns the
rank given a value in the range.

I can't do the vlookup type solution I see referenced in the other posting
because I cannot add an additional column next to the range to hold the ranks
(i.e., RANK(B1, B1:B10) filled into A1:A10).

I'm trying to avoid writing a macro, but need a single function that could
return the value in a set for a given rank that does not require adding extra
data to the sheet. The result in a tie doesn't matter because its the same
value, and if the rank didn't exist because of ties, I would expect an NA
consistent with RANK(value, range) when the value doesn't occur in the range.

Select value from a range given a rank
 

The more I look at the formuls in the previous posts, I think Large does what
I need here.

I have a cell which has calculated the rank I need, but Rank also indicates
where the size is, and because rank is skipping over ties, it works perfectly
for me as an input for large

So given that I have a cell A1 that has a valid RANK that I need, I can use
LARGE(<range, A1) and it is returning the value I want.


"BRD88" wrote:

I need to get a value out of a range when I know the rank.

I would like a symmetric function to RANK(value, range) which returns the
rank given a value in the range.

I can't do the vlookup type solution I see referenced in the other posting
because I cannot add an additional column next to the range to hold the ranks
(i.e., RANK(B1, B1:B10) filled into A1:A10).

I'm trying to avoid writing a macro, but need a single function that could
return the value in a set for a given rank that does not require adding extra
data to the sheet. The result in a tie doesn't matter because its the same
value, and if the rank didn't exist because of ties, I would expect an NA
consistent with RANK(value, range) when the value doesn't occur in the range.

Select value from a range given a rank
 

So, does this mean that the problem is solved?

Pete

On Aug 30, 6:56 pm, BRD88 wrote:
The more I look at the formuls in the previous posts, I think Large does what
I need here.

I have a cell which has calculated the rank I need, but Rank also indicates
where the size is, and because rank is skipping over ties, it works perfectly
for me as an input for large

So given that I have a cell A1 that has a valid RANK that I need, I can use
LARGE(<range, A1) and it is returning the value I want.



"BRD88" wrote:
I need to get a value out of a range when I know the rank.

I would like a symmetric function to RANK(value, range) which returns the
rank given a value in the range.

I can't do the vlookup type solution I see referenced in the other posting
because I cannot add an additional column next to the range to hold the ranks
(i.e., RANK(B1, B1:B10) filled into A1:A10).

I'm trying to avoid writing a macro, but need a single function that could
return the value in a set for a given rank that does not require adding extra
data to the sheet. The result in a tie doesn't matter because its the same
value, and if the rank didn't exist because of ties, I would expect an NA
consistent with RANK(value, range) when the value doesn't occur in the range.- Hide quoted text -

- Show quoted text -