rank

[Lori Maurer]

I have a list of data that I need to rank from 1-?? based on two sets of
criteria. here is an example of what I need.

Customer Name Product Sales Rank

Customer A Product 1 100 1
Customer A Product 1 50 3
Customer A Product 1 75 2
Customer A Product 2 540 2
Customer A Product 2 390 3
Customer A Product 2 600 1

So my question is, when Product changes, I want the ranking to start over
again. Is there a formula I can use to do this? Can I use an if statement
combined with a rank? (The product is not a number, it's text)

Thank you!!!!!

 

[Kevin B]

View the help on the RANK() function. If tie values, which will be ranked
the same, read further down to view the correction formula for tied values in
the list.

 

[Leo Heuser]

I have a list of data that I need to rank from 1-?? based on two sets of
criteria. here is an example of what I need.

Customer Name Product Sales Rank

Customer A Product 1 100 1
Customer A Product 1 50 3
Customer A Product 1 75 2
Customer A Product 2 540 2
Customer A Product 2 390 3
Customer A Product 2 600 1

So my question is, when Product changes, I want the ranking to start over
again. Is there a formula I can use to do this? Can I use an if
statement
combined with a rank? (The product is not a number, it's text)

Thank you!!!!!

Hi Lori

One way, assuming your data in A2:C100 and assuming all elements
of the same product are grouped as shown:

In D2 enter this array formula as one line:

=MATCH(C2,LARGE(OFFSET($C$2,MATCH(B2,$B$2:$B$100,0)-1,0,
MATCH(B2,$B$2:$B$100,0)+COUNTIF($B$2:$B$100,B2)-1),
ROW(INDIRECT("1:"&COUNTIF($B$2:$B$100,B2)))),0)

The formula must be finished with <Shift><Ctrl><Enter> instead of just
<Enter>, also if edited later. If done correctly, Excel will display the
formula in the formula bar enclosed in braces { }. Don't enter these
braces yourself, they're Excel's way of showing, that the formula is an
array formula.

Copy D2 down with the fill handle (the little square in the lower corner of
the cell).