Ranking function: 0's giving #N/A

williamsripa

New Member
Joined
Dec 2, 2005
Messages
3
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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
 

williamsripa

New Member
Joined
Dec 2, 2005
Messages
3
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...
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
hmm.. I'm guessing you want:

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

instead of:

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

williamsripa

New Member
Joined
Dec 2, 2005
Messages
3
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.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,898
Members
431,772
Latest member
dannyboi1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top