RANK - dont include zeros

[calebfda]

I am trying to figure out how to use the rank function on a column of
data. The trick is that i dont want it to include or rank zero's. To
achieve a ranking the number should be a positive number. Here is what
i have so far.
I dont care if it duplicates a ranking as long as the numbers are
EXACTLY the same. like two items can be ranked 5 if they are both
$6.06


=RANK(I5,$I5:$I40,1)&" of "&COUNTIF($I$5:$I$40, ">0")

any help would be greatly appreciated.

 

[Pete_UK]

What do you have in the cells I5:I40 ? If there is a formula then
perhaps you can change it to:

=IF(your_formula <= 0, "", your_formula)

and copy it down as appropriate.

Then you can change the rank formula to:

=IF(I5="","",RANK(I5,$I5:$I40,1)&" of "&COUNTIF($I$5:$I$40, ">0"))

Hope this helps.

Pete

 

[calebfda]

I5:i40 is just an uncalculated currency number.
would that formulat still work?

 

[Pete_UK]

Well, I would suggest that you do not type zeros into any of the cells
in I5:I40 - just leave them blank.

Then the second formula I gave you should work.

Hope this helps.

Pete

 

[calebfda]

Got it to work with
=IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/A")&" of "&COUNTIF($I$5:$I$40, ">0")

 

[Pete_UK]

Well, that's good to hear - thanks for feeding back.

Pete