RANK - dont include zeros

  • Thread starter Thread starter calebfda
  • Start date Start date
C

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.
 
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
 
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
 
Got it to work with
=IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/A")&" of "&COUNTIF($I$5:$I$40, ">0")
 
Back
Top