Excel Formula - Rank

Question

I am trying to figure out how to rank values based on the percentages. Which ever cell has the highest percentage, should be ranked 1st. Then who ever has the 2nd highest percentage, should be ranked 2nd. Then same thing for 3rd value.

However, I am comparing 12 different values (using cells A1 to A12). How do I rank each value but ONLY show the first 3 highest percentages? I have been using the RANK value, however, I don't want all 12 values to be shown..so values from 4-12 should not display or should be hidden.

Also, I need it to be accurate within 0.1. For example, if one value is 18.5% and another is 18.7%, I need the 18.7% to be ranked higher, and not equal (which is what happens with the RANK formula).

Any idea how to do this?

Thanks,

Answer 1

With data in A1 through A12, in C1 enter:

=LARGE($A$1:$A$12,ROW())

and copy down through C4

To show fewer items, copy through C3, etc.

EDIT#1:

Leave the formulas in column C. In column D just enter 1, 2, 3.
Then in B1 enter:

=IFERROR(VLOOKUP(A1,$C$1:$D$3,2,FALSE),"")

and copy down. Here is an example:

EDIT#2:

In B2 enter:

=IF(RANK(A1,$A$1:$A$12,0)<4,RANK(A1,$A$1:$A$12,0),"")

and copy down: