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

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

...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

=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

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

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

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

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.