formula to rank selected range of numbers ignoring the blank

[dhananjaywalke]

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

 

[Domenic]

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]

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]

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!