Pushing the envelope with RANK

[mark.wolven]

Here's what I am trying to do:

I have a historical list of data (achievements like past test scores)
in a range on sheet 2, for example, A1:A100

On sheet one, a new individual is tested and a score is generated in
B1. Without adding that score to the range on sheet 2, is there a way
to display the rank of the new individual's score?

Since the value to be ranked needs to be in the range of numbers, this
formula, =RANK(B1,Sheet2!A1:A100), evaluates to #NA.

Is there a different function that would do this?

 

[Shawn Morneau]

This would be a perfect use for the VLOOKUP function. On your sheet
2, sort your past achievements Ascending and add a column to the right
of it. Enter your ranking however you see fit (eg. 1,2,3,4,...,etc.)

Then, you can enter this formula into C1 to return the rank of the
score entered into B1.

=VLOOKUP(B1,Sheet2!A1:A100,2,True)

The last argument of that variable is what allows the for 'closest'
results to be found. If the score is not found in the range, the next
largest score is found and it's rank is returned.

Hope that gets you rolling.

-Shawn