# Thread: Rank Formula with Constraints (Excel 2000 SR-1)

1. ## Rank Formula with Constraints (Excel 2000 SR-1)

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.

2. ## Re: Rank Formula with Constraints (Excel 2000 SR-1

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(I NDIRECT("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(I NDIRECT("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.

3. ## Re: Rank Formula with Constraints (Excel 2000 SR-1

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.