# formula to rank selected range of numbers ignoring the blank

#### dhananjaywalke

##### Board Regular
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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?

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

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!

Replies
4
Views
104
Replies
3
Views
298
Replies
10
Views
205
Replies
5
Views
166
Replies
12
Views
279

1,220,950
Messages
6,157,028
Members
451,392
Latest member
malcv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back