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. =/
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
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
7,988
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
7,988
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,511
Members
412,599
Latest member
Schu94
Top