Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in ranking Formula | Excel Worksheet Functions | |||
Formula for Ranking | Excel Discussion (Misc queries) | |||
Ranking Formula Question | Excel Discussion (Misc queries) | |||
Ranking Formula | Excel Discussion (Misc queries) | |||
Ranking formula question? | Excel Discussion (Misc queries) |