Ranking function: 0's giving #N/A

[williamsripa]

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

 

[Oaktree]

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
	A	B	C	D
1	Value	Rank
2	0	1
3	1	2
4	2	3
5	3	4
Sheet1

 

[williamsripa]

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]

hmm.. I'm guessing you want:

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

instead of:

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

 

[williamsripa]

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]

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.