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...
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)