Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files
    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks, Aladin!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •