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 some time now. Above Is the formula for reverse rank. 1st I need to make it ignore and cells in the range that =0. 2nd I need it to only run if A1=2. If you could help me I would be so happy. Honestly wish I had the knowledge to contribute.
=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 some time now. Above Is the formula for reverse rank. 1st I need to make it ignore and cells in the range that =0. 2nd I need it to only run if A1=2. If you could help me I would be so happy. Honestly wish I had the knowledge to contribute.
4 Replies
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)
- Hi I understand where your coming from but the problem is that the list I am applying this to won't always have the same number of 0's as it is a dynamic list.
