formula to rank selected range of numbers ignoring the blank

dhananjaywalke

Board Regular
Joined
Jun 26, 2006
Messages
60
formula to rank selected range of numbers ignoring the blank if any.

I have 29 numbers in a column.

I want to rank them 1 to 5. But if any of the 29 numbers are zero then formula ranks them as 1 & then proceed to rank 2 3 4. I want formula to ignore zero or blank & rank minimum non zero as 1st.

Could anybody help me?

Thanks
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
Assuming that A2:A30 contains the data, try...

B2, copied down:

=IF(A2<>0,SUMPRODUCT(--($A$2:$A$30<>0),--(A2>$A$2:$A$30))+1,"")

Or are you looking for a Top 5 list?
 

dhananjaywalke

Board Regular
Joined
Jun 26, 2006
Messages
60
Yes Domenic

I want top five

also worst 5 out of these number.

i want to rank then & i dont want the number which is top 1 or worst 1
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
The following will return a Top N list, where 0's and the Top 1 are ignored. Assuming that A2:A30 contains the data...

B2: 5

(This indicates that you'd like a Top 5 list. Change this as desired.)

C2:

=SUM(IF(A2:A30<>0,IF(ISNUMBER(MATCH(A2:A30,SMALL(IF(A2:A30<>0,A2:A30),ROW(INDEX(A:A,2):INDEX(A:A,B2+1))),0)),1)))

...confirmed with CONTROL+SHIFT+ENTER.

D2, copied down:

=IF(ROWS($D$2:D2)<=$C$2,SMALL(IF($A$2:$A$30<>0,$A$2:$A$30),ROWS($D$2:D2)+1),"")

...also confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,983
Messages
5,545,340
Members
410,678
Latest member
glasi
Top