Re: Rank function ignoring only zero values
Quote from Rowddawg;683777Take 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.
That did work thanks!