Ranking Formula

[carl]

My data looks like this:

Date Product Sales Rank
1-Mar ABC 25 2
1-Mar DEF 50 1
1-Mar GHI 10 4
1-Mar JKL 15 3
1-Mar MNO 10 4
2-Mar GHI 100 2
2-Mar JKL 200 1
2-Mar MNO 25 3

I am trying to find a formula for ColD (Rank) that will place the rank of
the product (based on Sold) for each day.

Thank you in advance.

 

[Alan]

With the sales in C2:C9 enter one of these in D2 and drag down to D9

=RANK(C2,$C$2:$C$9) ranking descending

=RANK(C2,$C$2:$C$9,1) ranking ascending

Regards,
Alan.

 

[carl]

Thank you.

I was hoping to have a formula do the rank for a given day. I think your
formula does not look at the day ?

 

[Alan]

I'm sorry, I misunderstood the question. I can't really see how it can be
done with just one formula because the RANK function needs the cell
references defined. I think you would have to use a separate formula for
each day. Hopefully someone will prove me wrong!
Regards,
Alan.

 

[Teethless mama]

Try this:
Date is a defined name range A2:A9

=RANK(C2,OFFSET($C$2,MATCH(A2,Date,)-1,,MATCH(A2,Date),1))
copy down

 

[Herbert Seidenberg]

Excel 2007 Table
Rank within subcategories.
One formula for all days, four formula variations.
http://www.mediafire.com/file/r4ndymwettv/04_06_09c.xlsx

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A$2:A$9=A2),--(C2<C$2:C$9))+1

Copy down as needed.