Rank IF Ascending Order

[dversloot1]

Hello,

I'm trying to rank in ascending order the top 20 lowest margin generating items by week. I'm stumped on what sort of formula to use for this.
I will have a data tab consisting of historical data from previous weeks. I would like to be able to call out the top 20 margin loss drivers by week using a Selected week from a drop down menu.

Any ideas on what the formula should be for the rank column by week?

Rank Week Margin Product
? 1 $96 A
? 1 $54 B
? 1 $88 C
? 2 $22 A
? 2 $36 B
? 2 $31 C

 

[oldbrewer]

There is a formula called small and small,1 = smallest - small,2 is next - check it out

 

[Aladin Akyurek]

Hello,

I'm trying to rank in ascending order the top 20 lowest margin generating items by week. I'm stumped on what sort of formula to use for this.
I will have a data tab consisting of historical data from previous weeks. I would like to be able to call out the top 20 margin loss drivers by week using a Selected week from a drop down menu.

Any ideas on what the formula should be for the rank column by week?

Rank Week Margin Product
? 1 $96 A
? 1 $54 B
? 1 $88 C
? 2 $22 A
? 2 $36 B
? 2 $31 C

Try to formulate the problem in terms of the sample you posted and post the expected results.

 

[dversloot1]

Try to formulate the problem in terms of the sample you posted and post the expected results.

I believe this ranks the margin in descending order, not Ascending:
A2=SUMPRODUCT(--(B$2:B$7=B2)*(C2<C$2:C$7))+1

Expected Results:

Rank Week Margin Product
3 1 $96 A
1 1 $54 B
2 1 $88 C
1 2 $22 A
3 2 $36 B
2 2 $31 C

 

[Aladin Akyurek]

I believe this ranks the margin in descending order, not Ascending:
A2=SUMPRODUCT(--(B$2:B$7=B2)*(C2<c$2:c$7))+1

Expected Results:

Rank Week Margin Product
3 1 $96 A
1 1 $54 B
2 1 $88 C
1 2 $22 A
3 2 $36 B
2 2 $31 C

Either...

</c$2:c$7))+1
=SUMPRODUCT(--($B$2:$B$7=B2),--(C2>$C$2:$C$7))+1

Or...

=COUNTIFS($B$2:$B$7,B2,$C$2:$C$7,"<"&C2)+1

<c$2:c$7))+1



</c$2:c$7))+1