Forum Discussion
ignoring 0 and nesting in if
Ok so I am dealing with two problems =RANK(B3,$B$3:$B$23,1) - ( COUNTIF($B$3:$B$23, "<"&B3)- SUMPRODUCT(($B$3:$B$23<B3)*1/COUNTIF($B$3:$B$23,$B$3:$B$23)) ) I've been working on a project for quite s...
Hi Thomas,
If i understood correctly what do you mean under zero ignoring you may substruct number of zeros from your result, other words add to the end of your formula
-COUNTIF($B$3:$B$23,0)
And to add condition could be something like
=($A$1=2)*(<your formula>)
Also well untested in theory it seems like it would throw the ranks off making a lot of them negative results. so if I subtracted 10 wouldn't rank 1 become -9
Thomas, yes, only one time to deduct for all zeros
=RANK(B3,$B$3:$B$23,1) - ( COUNTIF($B$3:$B$23, "<"&B3)- SUMPRODUCT(($B$3:$B$23<B3)*1/COUNTIF($B$3:$B$23,$B$3:$B$23)) )-(COUNTIF($B$3:$B$23,0)>0)