Rank function problem

**Kolacube** · 04-04-2011, 02:39 AM

Hi there
I have a problem with getting my rank function to work properly, I understand if I list all the numbers consecutively it will work, but I want my spreadsheet to look like the attached. You will see I am trying to pick up the top 5, but it misses 4 and 1.

Any help appreciated.
Thanks

**brokenbiscuits** · 04-04-2011, 03:03 AM

You need to use commas instead of colons to avoid the 'total' lines:

=IF(L12=0,"",IF(ISNA(RANK(L12,($L$9:$L$14,$L$17:$L$23,$L$26:$L$28,$L$31:$L$33))),"",RANK(L12,($L$9:$L$14,$L$17:$L$23,$L$26:$L$28,$L$31:$L$33))))

**martindwilson** · 04-04-2011, 05:18 AM

make sure you have the word "total" in the total rows on column a
then rank from p12 down =IF(OR(A12="total",L12=0,ISNA(RANK(L12,($L$12:$L$33)))),"",RANK(L12,($L$12:$L$33))) (note if you have duplicate values the will rank = and the index match wont work later so you may need to use unique rank) now in k2 dragged down put
=INDEX($A:$A,MATCH(SMALL($P:$P,ROW(A1)),$P:$P,0))