# Ranking function: 0's giving #N/A

#### williamsripa

##### New Member
I want to rank a group of numbers (from formulas in other cells) using the rank function, but when it encounters a 0 it displays it as #N/A and throws off the entire list.

I need these 0's to display in the right order in the list... obviously 0 is a lower number than 1 and should be ranked first at the top in ascending order!

The numbers that it's trying to rank all come from formulas that take one cell and divide it by another, and there are often 0's in these cells also, but I can use the IF function to force it to display "0" in those cases.

Is there an option that will make it rank zeroes properly? the exact function in the formula bar I'm using is something like:

=RANK(G23,G3:G29,1)

even if i use absolute values it still doesnt work.

=RANK(G23,\$G\$3:\$G:\$29,1)

I wish i could just tell it that 0 divided by 0 is 0. =/

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Welcome to the board.

Check that there are are no #N/A's in your range.

The example below shows 0's ranked correctly....

B2 =RANK(A2,\$A\$2:\$A\$5,1) copied down.
Book4
ABCD
1ValueRank
201
312
423
534
Sheet1

Well, the formula in the referenced cell will sometimes come back with #N/A because it's trying to divide 0 by 0.

but for all the referenced cells, I have:

=IF(F7=0,"0",F7/E7)

Then I want to rank those. So if F7 and E7 are both zero, the formula would normally return a #N/A, but with the IF statement, it displays a 0.

I want this last 0 included in my rankings though...

hmm.. I'm guessing you want:

=IF(E7=0,0,F7/E7)

=IF(F7=0,"0",F7/E7)

Yes! that was it! thank you.

the quotations mean that it keeps the value of #N/A but just *displays* a zero. I wanted it to retain the value of zero instead of just displaying it.

Not quite... What was really going on was that the quotes were putting the text value 0 in the cell instead of the number value 0. The rank function only works with numbers.

Replies
3
Views
127
Replies
14
Views
893
Replies
3
Views
116
Replies
1
Views
522
Replies
0
Views
190

1,217,385
Messages
6,136,276
Members
450,001
Latest member
KWeekley08

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