Rank Formula with Constraints (Excel 2000 SR-1)

Topic

New Reply

Perhaps there’s an easy way to do ranking with constraints – I have a number of stores each having 3 products and need to find a way to do two rankings. The first would be to rank the products within a store (ie RANK(C2, [range selection containing identical store numbers]), and the second would be to rank each product across all stores (ie RANK(C2, [range selection containing identical product IDs).

Hopefully viewing the attached file would help make my description more clear…

Of course, I could just sort the data in the 2 ways, apply the standard ranking, paste/special values and re-sort back to the original data configuration, but I would like to learn a more automated way!

Any ideas/help would be appreciated. Thanks.

Reply | Quote

Replies

Given that A1:C16 houses the following data

{“Store”,”Product”,”Sales”;
1,”A”,10;
1,”B”,2;
1,”C”,5;
2,”A”,13;
2,”B”,22;
2,”C”,24;
3,”A”,1;
3,”B”,20;
3,”C”,12;
4,”A”,14;
4,”B”,7;
4,”C”,19;
5,”A”,9;
5,”B”,4;
5,”C”,6}

in D2 array-enter: =MATCH(C2,LARGE(IF($A$2:$A$16=A2,$C$2:$C$16),ROW(INDIRECT(“1:”&COUNTIF($A$2:$A$16,A2)))),0)

in G2 array-enter: =MATCH(C2,LARGE(IF($B$2:$B$16=B2,$C$2:$C$16),ROW(INDIRECT(“1:”&COUNTIF($B$2:$B$16,B2)))),0)

You need to hit control+shift+enter at the same time, not just enter, in order to enter an array formula.

Reply | Quote

Brilliant! Thank you very much for your insight – it worked perfectly, has saved considerable time, and has now encouraged me to learn more about indirect and match.

Thanks, Aladin!

Reply | Quote