# Ascending RankIf with Conditions

#### studious

##### New Member
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.
 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?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### CyrusTheVirus

##### Well-known Member
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:
Code:
=IF(OR(B2=0,A2=0),"",A2)

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

D2 (option 2):
Code:
=IF(C2="","",RANK.EQ(C2,\$C\$2:\$C\$8,1))
 Value Filter Filter Value Rank 1.3 1 1.3 1 2.1 1 2.1 2 2.3 2.0 3.1 2.5 0.0 1

<tbody>
</tbody>

Last edited:

#### Brian from Maui

##### MrExcel MVP
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.
 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 2.0 3.1 2.5 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?

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

##### New Member
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.

Last edited:

Replies
3
Views
135
Replies
3
Views
571
Replies
5
Views
154
Replies
7
Views
896
Replies
5
Views
189

1,191,200
Messages
5,985,237
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back