ranking with some blank spaces

  • Thread starter LRR via OfficeKB.com
  • Start date
L

LRR via OfficeKB.com

I am creating a ranking sheet with 20 rows. There will be a differing amount
of numbers to rank. Sometimes there may only be 10 numbers to rank and
sometimes more or less. How do you prevent the blank spaces from being part
of your ranking? In other words, if I have 10 numbers, I want to rank 1
through 10 and ignore the spaces 11 through 20.
 
D

daddylonglegs

If your numbers are in A2:A21 then use this formula in B2 copied down

=IF(A2<>"",SUMPRODUCT(--(A$2:A$21<>""),--(A$2:A$21>A2))+1,"")

note: this asssumes that you want to rank from highest to lowest....
 
D

daddylonglegs

...in fact you could simplify that formula to

=IF(A2<>"",COUNTIF(A$2:A$21,">"&A2)+1,""
 
L

LRR via OfficeKB.com

Works great so far. Now what if I want to rank from lowest to highest?
Meaning lowest number is "number 1".
 
D

daddylonglegs

You can adjust Ardus' suggestion to

=IF(ISNA(RANK(A1,A$1:A$20,1)),"",RANK(A1,A$1:A$20,1))

or use

=IF(A1="","",COUNTIF(A$1:A$20,"<"&A1)+1)
 
L

LRR via OfficeKB.com

Thanks. Works great. It's counting the zero fields in the ranking so a
field with zero is "number one" in the ranking. I just put a big number in
those fields and it works. You've all been a great help.
 
A

Aladin Akyurek

If you mean empty cells by "blank spaces"...

=IF(ISNUMBER(A2),RANK(A2,$A$2:$A$21,1),"")
 
Top