Ascending RankIf with Conditions

[studious]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Filter[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]1[/TD]
[TD]{=RANK.AVG(A2,IF($B$2:$B$8=1,$B$2:$B$8),1)}[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[CyrusTheVirus]

You could probably do this with a similar method that you're trying, but why not just use a helper column? Helper columns can be very useful, then can simplify what you're trying to do by allowing for more easily understandable formulas and can cut down on calculation time.

C2:

=IF(OR(B2=0,A2=0),"",A2)

D2 (option 1):

=IFERROR(RANK.EQ(C2,$C$2:$C$8,1),"")

D2 (option 2):

=IF(C2="","",RANK.EQ(C2,$C$2:$C$8,1))

[TABLE="width: 262"]
<tbody>[TR]
[TD]Value
[/TD]
[TD]Filter
[/TD]
[TD]Filter Value
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD="align: right"]1.3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.3
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]2.1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2.1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2.3
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.0
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.5
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.0
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Brian from Maui]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value
[/TD]
[TD]Filter
[/TD]
[TD]Formula
[/TD]
[/TR]
[TR]
[TD]1.3
[/TD]
[TD]1
[/TD]
[TD]{=RANK.AVG(A2,IF($B$2:$B$8=1,$B$2:$B$8),1)}
[/TD]
[/TR]
[TR]
[TD]2.1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.0
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.0
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

Using a two step method, where Column K houses Values and Column L filter. In M, enter and drag down

=IF(AND(K3 > 0,L3=1),RANK(K3,$K$3:$K$8,1),"")

In N, enter and drag down

=IF(N(M3),RANK(M3,$M$3:$M$8,1)+COUNTIF($M$3:M3,M3)-1,"")



</a2))+1,"")

 

[studious]

Thank you. Went ahead and created helper tables. I had to do it for many columns, which is why I was asking for a clever way to do it, but it would have complicated things more than just creating the helper tables.