I am trying to rank (with a formula, no filters) some values using the values in another column as filters while excluding zeros from the ranking.
<tbody>
</tbody>
I get a #VALUE error, because I am trying to rank the value against a binary array.
Then I tried a fancier formula, which works for the ranking excluding zeros bit but disregards the filter.
=IF(N(A2),SUMPRODUCT(1-($A$2:$A$8=0)*($B$2:$B$8=1),--($A$2:$A$8<A2))+1,"")
I care only about the rank of the first entry. So, in my example Value 1.3 would rank 1 because it is the lowest value within the array of 3 values marked by 1 in Filter. The last value would be ignored in the ranking although it is flagged, because it is a zero.
Do you have any suggestions how to achieve this in a formula?
Value | Filter | Formula |
1.3 | 1 | {=RANK.AVG(A2,IF($B$2:$B$8=1,$B$2:$B$8),1)} |
2.1 | 1 | |
2.3 | 0 | |
2.0 | 0 | |
3.1 | 0 | |
2.5 | 0 | |
0.0 | 1 |
<tbody>
</tbody>
I get a #VALUE error, because I am trying to rank the value against a binary array.
Then I tried a fancier formula, which works for the ranking excluding zeros bit but disregards the filter.
=IF(N(A2),SUMPRODUCT(1-($A$2:$A$8=0)*($B$2:$B$8=1),--($A$2:$A$8<A2))+1,"")
I care only about the rank of the first entry. So, in my example Value 1.3 would rank 1 because it is the lowest value within the array of 3 values marked by 1 in Filter. The last value would be ignored in the ranking although it is flagged, because it is a zero.
Do you have any suggestions how to achieve this in a formula?