can you rank more than 7 items? how?

[EMIResearch]

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(RANK(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"",IF(RANK(F13,F13:M13,0)=8,""))))))))

[bpeltzer]

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(RANK(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"",IF(RANK(F13,F13:M13,0)=8,""))))))))

[Gary''s Student]

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(RANK(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"",IF(RANK(F13,F13:M13,0)=8,""))))))))

[EMIResearch]

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(RANK(F13,F13:M13,0)=6,"",IF(RANK(F13,F13:M13,0)=7,"",IF(RANK(F13,F13:M13,0)=8,""))))))))

[daddylonglegs]

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