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?