Ranking with Condition

[Tom Kosensky]

I will start with an example:

Column A Column B
3.98 25
2.55 6
3.12 15
4.55 14
6.02 33
9.00 6
1.90 11
-----------------------------------------------
What I need:

I need a formula that will rank the numbers in Column A, but only if Column
B has a value >=10. In other words, in the example above, the rankings
would be:

Column A Column B Column C (Rank)
3.98 25 3
2.55 6 N/A
3.12 15 4
4.55 14 2
6.02 33 1
9.00 6 N/A
1.90 11 5

Scores 9.00 and 2.55 are excluded from the ranking because they don't have a
base size of 10 or greater.

Does anyone know how this can be done? Thanks!!!

 

[Myrna Larson]

Ordinarily you would solve this with and array formula. Unfortunately RANK doesn't work
correctly in an array formula, so you'll need to use a helper column.

Let's say that's C. In C1 put the formula =IF(B1>=10,A1,"")and copy it down. In D1 put this
formula =IF(C1="","N/A",RANK(C1,$C$1:$C$100)). Adjust the last row in the RANK formula as
needed.