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 "false" in the
cell and I need it to be blank.

Here's my formula that works, but delivers a "False" on the 8th rank.
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"
",IF(RANK(F13,F13:M13,0)=5,"",IF(RANK(F13,F13:M13, 0)=6,"",IF(RANK(F13,F13:M13,0)=7,"""")))))))

Here is my formula with the 8th rank, that comes back with an error...
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"",IF(RANK(F13,F13:M13,0)=5,"",IF(RAN K(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"", IF(RANK(F13,F13:M13,0)=8,""))))))))

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)3,"",CHOOSE(RANK(F13,$F$ 13:$M$13),"u","v","w")).
--Bruce

"EMIResearch" wrote:

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 "false" in the
cell and I need it to be blank.

Here's my formula that works, but delivers a "False" on the 8th rank.
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"
",IF(RANK(F13,F13:M13,0)=5,"",IF(RANK(F13,F13:M13, 0)=6,"",IF(RANK(F13,F13:M13,0)=7,"""")))))))

Here is my formula with the 8th rank, that comes back with an error...
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"",IF(RANK(F13,F13:M13,0)=5,"",IF(RAN K(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"", IF(RANK(F13,F13:M13,0)=8,""))))))))

Use CHOOSE():


=CHOOSE(RANK(F13,F13:M13,0),"u","v","w","","",""," ","")

Read about CHOOSE() in help. Its perfect for avoiding nested IF's
--
Gary's Student


"EMIResearch" wrote:

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 "false" in the
cell and I need it to be blank.

Here's my formula that works, but delivers a "False" on the 8th rank.
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"
",IF(RANK(F13,F13:M13,0)=5,"",IF(RANK(F13,F13:M13, 0)=6,"",IF(RANK(F13,F13:M13,0)=7,"""")))))))

Here is my formula with the 8th rank, that comes back with an error...
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"",IF(RANK(F13,F13:M13,0)=5,"",IF(RAN K(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"", IF(RANK(F13,F13:M13,0)=8,""))))))))

Bruce - thank you so much! I used your [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.

"bpeltzer" wrote:

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)3,"",CHOOSE(RANK(F13,$F$ 13:$M$13),"u","v","w")).
--Bruce

"EMIResearch" wrote:

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 "false" in the
cell and I need it to be blank.

Here's my formula that works, but delivers a "False" on the 8th rank.
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"
",IF(RANK(F13,F13:M13,0)=5,"",IF(RANK(F13,F13:M13, 0)=6,"",IF(RANK(F13,F13:M13,0)=7,"""")))))))

Here is my formula with the 8th rank, that comes back with an error...
=IF(RANK(F13,F13:M13,0)=1,"u",IF(RANK(F13,F13:M13, 0)=2,"v",IF(RANK(F13,F13:M13,0)=3,"w",IF(RANK(F13, F13:M13,0)=4,"",IF(RANK(F13,F13:M13,0)=5,"",IF(RAN K(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"", IF(RANK(F13,F13:M13,0)=8,""))))))))

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

=CHOOSE(MIN(4,RANK(F13,F13:M13)),"u","v","w","")

or even

=INDEX({"u","v","w",""},MIN(4,RANK(F13,F13:M13)))


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