|
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... |
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... |
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... |
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... |
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... |
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... |
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... |
| All times are GMT +1. The time now is 03:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com