 # Rank function ignoring only zero values

• Hello,

I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?

=IF(D94=0,"",RANK(D94,\$D\$94:\$AY\$94,0))

Thanks.

• Re: Rank function ignoring only zero values

Can you attach a copy of your workbook?

• Re: Rank function ignoring only zero values

I've attached a simplified version of the spreadsheet. Thanks.

## Files

• Ranking.xlsx

• Re: Rank function ignoring only zero values

Take your sample worksheet and place this where d5 starts and copy across the entire row. Should give you what you're looking for.

=IF(D4>0,(IF(D4=0,0,RANK(D4,\$D\$4:\$AY\$4,0))),IF(D4=0,0,(IF(D4=0,0,RANK(D4,\$D\$4:\$AY\$4,0)))-(COUNTIF(\$D\$4:\$AY\$4,"=0"))))

Assuming that works, this should work then on your actual sheet...

=IF(D94>0,(IF(D94=0,0,RANK(D94,\$D\$94:\$AY\$94,0))),IF(D94=0,0,(IF(D94=0,0,RANK(D94,\$D\$94:\$AY\$94,0)))-(COUNTIF(\$D\$94:\$AY\$94,"=0"))))

Have to say this stumped me for a while. Enjoyed it.
Let me know if it works for you please.

• Re: Rank function ignoring only zero values

That did work thanks!

• Hi Rowddawg,

Thanks for the formula. In my spreadsheet it is ignoring the zeros but the ranking numbering only commences once the number of zeros has been factored in. For example, if there are two cells containing zero the ranking commences at 3. With five cells containing a 0 the ranking commences at 6. Is there a way to make the lowest score bar zero have a ranking of one?

Many thanks

• Maybe I don't understand, but:

=IF(D4=0,0,IF(D4<>0,RANK(D4,\$D\$4:\$AY\$4,0)))

it is all you need.

and if you don't want zero, then:

=IF(D4=0,"",IF(D4<>0,RANK(D4,\$D\$4:\$AY\$4,0)))

• Thanks Tom,

[TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

0:00:00.001

[/td]

[td]

6

[/td]

[/tr]

[tr]

[td]

0:00:00.100

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[/TABLE]

I was expecting B1 to be 1 and B2 equal to 2 but the five 0s have been factored into the ranking order.

Is there something that needs to be added to the forula to avoid this?

• I have found a work around:

=IF(E3=0,0,IF(E3<>0,RANK(E3,\$E\$3:\$E\$9,1)))-COUNTIF(\$E\$3:\$E\$9,0)

Any negative numbers are not included in the ranking order so in the above example B1 now equals 1 and B2 now equals 2.

• From where you post that data???