Ranking Formula
 

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.

Ranking Formula
 

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" wrote in message
...
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.

Ranking Formula
 

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" wrote:

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" wrote in message
...
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.

Ranking Formula
 

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.
"carl" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Ranking Formula
 

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


"carl" wrote:

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.

Ranking Formula
 

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

Ranking Formula
 

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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.