RANK sublist

[Sean Timmons]

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...

 

[T. Valko]

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.

 

[Bernie Deitrick]

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]

Very nice. So --(C2<C$2:C$7) returns number of values greater than C2...
Would have thought that would return 0 or 1...

 

[Sean Timmons]

Ahh, calculating the max date for me! thank you for the extra add!

 

[T. Valko]

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

 

[T. Valko]

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...)