ranking with some blank spaces
 

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.

--
Message posted via http://www.officekb.com

ranking with some blank spaces
 

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$21A2))+1,"")

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533150

ranking with some blank spaces
 

...in fact you could simplify that formula to

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533150

ranking with some blank spaces
 

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

HTH
--
AP

"LRR via OfficeKB.com" <u20393@uwe a écrit dans le message de
news:5ed16e1d38230@uwe...
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.

--
Message posted via http://www.officekb.com

ranking with some blank spaces
 

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

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

HTH
--
AP

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1

ranking with some blank spaces
 

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533150

ranking with some blank spaces
 

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.

daddylonglegs wrote:
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)


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200604/1

ranking with some blank spaces
 

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

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

LRR via OfficeKB.com wrote:
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.