| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK sublist
So, I have a worksheet with a few thousand rows.
There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
Assuming the dates are all within the same year:
=IF(MONTH(A2)<7,"",SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(C2<C$2:C$7))+1) Copy down as needed. -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
Sean,
With your data table in A1:C6, enter this in D1: =IF(MONTH(A1)=MONTH(MAX($A$1:$A$6)),1+SUMPRODUCT(( MONTH($A$1:$A$6)=MONTH(MAX($A$1:$A$6)))*($C$1:$C$6 C1)),"") and copy down. HTH, Bernie MS Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
Very nice. So --(C2<C$2:C$7) returns number of values greater than C2...
Would have thought that would return 0 or 1... "T. Valko" wrote: Assuming the dates are all within the same year: =IF(MONTH(A2)<7,"",SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(C2<C$2:C$7))+1) Copy down as needed. -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
Ahh, calculating the max date for me! thank you for the extra add!
"Bernie Deitrick" wrote: Sean, With your data table in A1:C6, enter this in D1: =IF(MONTH(A1)=MONTH(MAX($A$1:$A$6)),1+SUMPRODUCT(( MONTH($A$1:$A$6)=MONTH(MAX($A$1:$A$6)))*($C$1:$C$6 C1)),"") and copy down. HTH, Bernie MS Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
Would have thought that would return 0 or 1...
If Cn is the max that meets the criteria (month=7) then: SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7)) =0 (there are no numbers less than Cn because Cn is the max) So we add 1: SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))+1 -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Very nice. So --(C2<C$2:C$7) returns number of values greater than C2... Would have thought that would return 0 or 1... "T. Valko" wrote: Assuming the dates are all within the same year: =IF(MONTH(A2)<7,"",SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(C2<C$2:C$7))+1) Copy down as needed. -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK sublist
SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))
=0 (there are no numbers less than Cn because Cn is the max) Not phrased very well! SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7)) =0 (because Cn is *not less than* any numbers in C2:C7...) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Would have thought that would return 0 or 1... If Cn is the max that meets the criteria (month=7) then: SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7)) =0 (there are no numbers less than Cn because Cn is the max) So we add 1: SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))+1 -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Very nice. So --(C2<C$2:C$7) returns number of values greater than C2... Would have thought that would return 0 or 1... "T. Valko" wrote: Assuming the dates are all within the same year: =IF(MONTH(A2)<7,"",SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(C2<C$2:C$7))+1) Copy down as needed. -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... So, I have a worksheet with a few thousand rows. There is one row per month per employee 5/31/09 Bob A 2.5 6/30/09 Bob A 1.9 7/31/09 Bob A 2.3 5/31/09 John S 1.6 6/30/09 John S 2.0 7/31/09 John S 2.9 etc. I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for John S), and return a 1 on John S.'s July line and a 2 on Bob A.s July line, with blanks in the others. I can simply use MAX() to get the most current date, but not sure how to get my RANK to only rank those scores in July. Always getting a 3 for Bob a. in the above... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Form a sublist from a list using lookup | Excel Discussion (Misc queries) | |||
| rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
| RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
| Drop Down Sublist | Excel Discussion (Misc queries) | |||
| Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Linear Mode
