software

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 www.officekb.com


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

=IF(A2lt;gt;quot;quot;,SUMPRODUCT(--(A$2:A$21lt;gt;quot;quot;),--(A$2:A$21gt;A2)) 1,quot;quot;)

note: this asssumes that you want to rank from highest to lowest.....--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533150
...in fact you could simplify that formula to

=IF(A2lt;gt;quot;quot;,COUNTIF(A$2:A$21,quot;gt;quot;amp;A2) 1,quot;quot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533150=IF(ISNA(RANK(A1,A$1:A$20)),quot;quot;,RANK(A1,A$1:A$20))

HTH
--
AP

quot;LRR via OfficeKB.comquot; lt;u20393@uwegt; a 嶰rit dans le message de
news:5ed16e1d38230@uwe...
gt; I am creating a ranking sheet with 20 rows. There will be a differing
amount
gt; of numbers to rank. Sometimes there may only be 10 numbers to rank and
gt; sometimes more or less. How do you prevent the blank spaces from being
part
gt; of your ranking? In other words, if I have 10 numbers, I want to rank 1
gt; through 10 and ignore the spaces 11 through 20.
gt;
gt; --
gt; Message posted via www.officekb.com
Works great so far. Now what if I want to rank from lowest to highest?
Meaning lowest number is quot;number 1quot;.

Ardus Petus wrote:
gt;=IF(ISNA(RANK(A1,A$1:A$20)),quot;quot;,RANK(A1,A$1:A$20 ))
gt;
gt;HTH
gt;--
gt;AP
gt;
gt;gt; I am creating a ranking sheet with 20 rows. There will be a differing amount
gt;gt; of numbers to rank. Sometimes there may only be 10 numbers to rank and
gt;gt; sometimes more or less. How do you prevent the blank spaces from being part
gt;gt; of your ranking? In other words, if I have 10 numbers, I want to rank 1
gt;gt; through 10 and ignore the spaces 11 through 20.

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


You can adjust Ardus' suggestion to

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

or use

=IF(A1=quot;quot;,quot;quot;,COUNTIF(A$1:A$20,quot;lt;quot;amp;A1) 1)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533150Thanks. Works great. It's counting the zero fields in the ranking so a
field with zero is quot;number onequot; in the ranking. I just put a big number in
those fields and it works. You've all been a great help.

daddylonglegs wrote:
gt;You can adjust Ardus' suggestion to
gt;
gt;=IF(ISNA(RANK(A1,A$1:A$20,1)),quot;quot;,RANK(A1,A$1:A$20 ,1))
gt;
gt;or use
gt;
gt;=IF(A1=quot;quot;,quot;quot;,COUNTIF(A$1:A$20,quot;lt;quot;amp;A1) 1)
gt;

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

If you mean empty cells by quot;blank spacesquot;...

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

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