Pushing the Envelope with the RANK function

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

 

[Pete_UK]

One way of doing this is to insert a row at the top of sheet2 and in
A1 put the formula:

=Sheet1!B1

Then in Sheet1 you can use this formula:

=RANK(B1,Sheet2!A1:A101)

Although you are adding the value to the list, this is only temporary
and obviously other values can be placed in B1 to give you other
rankings.

Hope this helps.

Pete

 

[Leo Heuser]

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?

Mark

Here's one way:

=SUMPRODUCT((B1<Sheet2!A1:A100)+0)+1