software

I can't get Excel to rank more than 7 items (I have 8). Is there a limit to
how many it can do? When the result is a rank of 8, it puts quot;falsequot; in the
cell and I need it to be blank.

Here's my formula that works, but delivers a quot;Falsequot; on the 8th rank.
=IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;
quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RANK(F13,F13:M13, 0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;quot;quot;)))))))

Here is my formula with the 8th rank, that comes back with an error...
=IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RAN K(F13,F13:M13,0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;, IF(RANK(F13,F13:M13,0)=8,quot;quot;))))))))

You can't nest functions more than 7 levels deep. I'd suggest creating a
table with the ranks in the first column, and the associated letters (or
blanks) in the second column. Suppose that table is located at Sheet2!A1:B8.
Then your formula would be
=vlookup(rank(f13,$F$13:$M$13),Sheet2!$A$1:$B$8,2, false). If you're only
worried about associating letters with the top three, you might also use
=IF(RANK(F13,$F$13:$M$13)gt;3,quot;quot;,CHOOSE(RANK(F13,$F$ 13:$M$13),quot;uquot;,quot;vquot;,quot;wquot;)).
--Bruce

quot;EMIResearchquot; wrote:

gt; I can't get Excel to rank more than 7 items (I have 8). Is there a limit to
gt; how many it can do? When the result is a rank of 8, it puts quot;falsequot; in the
gt; cell and I need it to be blank.
gt;
gt; Here's my formula that works, but delivers a quot;Falsequot; on the 8th rank.
gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;
gt; quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RANK(F13,F13:M13, 0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;quot;quot;)))))))
gt;
gt; Here is my formula with the 8th rank, that comes back with an error...
gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RAN K(F13,F13:M13,0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;, IF(RANK(F13,F13:M13,0)=8,quot;quot;))))))))

Use CHOOSE():=CHOOSE(RANK(F13,F13:M13,0),quot;uquot;,quot;vquot;,quot;wquot;,quot;quot;,quot;quot;,quot;quot;,quot; quot;,quot;quot;)

Read about CHOOSE() in help. Its perfect for avoiding nested IF's
--
Gary's Studentquot;EMIResearchquot; wrote:

gt; I can't get Excel to rank more than 7 items (I have 8). Is there a limit to
gt; how many it can do? When the result is a rank of 8, it puts quot;falsequot; in the
gt; cell and I need it to be blank.
gt;
gt; Here's my formula that works, but delivers a quot;Falsequot; on the 8th rank.
gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;
gt; quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RANK(F13,F13:M13, 0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;quot;quot;)))))))
gt;
gt; Here is my formula with the 8th rank, that comes back with an error...
gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RAN K(F13,F13:M13,0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;, IF(RANK(F13,F13:M13,0)=8,quot;quot;))))))))

Bruce - thank you so much! I used your [gt;3, return nothing] idea and that
worked. Wish I had thought of that myself. I was making it more complicated
than it needed to be.

quot;bpeltzerquot; wrote:

gt; You can't nest functions more than 7 levels deep. I'd suggest creating a
gt; table with the ranks in the first column, and the associated letters (or
gt; blanks) in the second column. Suppose that table is located at Sheet2!A1:B8.
gt; Then your formula would be
gt; =vlookup(rank(f13,$F$13:$M$13),Sheet2!$A$1:$B$8,2, false). If you're only
gt; worried about associating letters with the top three, you might also use
gt; =IF(RANK(F13,$F$13:$M$13)gt;3,quot;quot;,CHOOSE(RANK(F13,$F$ 13:$M$13),quot;uquot;,quot;vquot;,quot;wquot;)).
gt; --Bruce
gt;
gt; quot;EMIResearchquot; wrote:
gt;
gt; gt; I can't get Excel to rank more than 7 items (I have 8). Is there a limit to
gt; gt; how many it can do? When the result is a rank of 8, it puts quot;falsequot; in the
gt; gt; cell and I need it to be blank.
gt; gt;
gt; gt; Here's my formula that works, but delivers a quot;Falsequot; on the 8th rank.
gt; gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;
gt; gt; quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RANK(F13,F13:M13, 0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;quot;quot;)))))))
gt; gt;
gt; gt; Here is my formula with the 8th rank, that comes back with an error...
gt; gt; =IF(RANK(F13,F13:M13,0)=1,quot;uquot;,IF(RANK(F13,F13:M13, 0)=2,quot;vquot;,IF(RANK(F13,F13:M13,0)=3,quot;wquot;,IF(RANK(F13, F13:M13,0)=4,quot;quot;,IF(RANK(F13,F13:M13,0)=5,quot;quot;,IF(RAN K(F13,F13:M13,0)=6,quot;quot;,IF(RANK(F13,F13:M13,0)=7,quot;quot;, IF(RANK(F13,F13:M13,0)=8,quot;quot;))))))))


You could amend Gary''s student's suggestion to

=CHOOSE(MIN(4,RANK(F13,F13:M13)),quot;uquot;,quot;vquot;,quot;wquot;,quot;quot;)

or even

=INDEX({quot;uquot;,quot;vquot;,quot;wquot;,quot;quot;},MIN(4,RANK(F13,F13:M13)))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515950