Max value in c, date in D, E, or F:
Note that XL doesn't really know difference between a date and a number (0 =
1/1/1900). So, the best we can do is check if there's a number.
=MAX((((ISNUMBER(D2:D100)+ISNUMBER(E2:E100)+ISNUMB ER(F2:F100))0)*(C2:C100)))
Input as array (Ctrl+Shift+Enter to confirm)
Value of C correlating to max in D-E:
Note that this assumes 1 unique max value
=INDEX(C2:C100,IF(ISERROR(MATCH(MAX(D2:E100),D2:D1 00,0)),MATCH(MAX(D2:E100),E2:E100,0),MATCH(MAX(D2: E100),D2:D100,0)))
Black characters:
Can't be done using XL formulas, would require
VB. Is there some condition
that makes certain cells blue (which could be tied into formula)
Last row with data in D, E, or F:
=MAX((((ISNUMBER(D2:D100)+ISNUMBER(E2:E100)+ISNUMB ER(F2:F100))0)*ROW(C2:C100)))
Input as array (Ctrl+Shift+Enter to confirm)
In all formulas, adjust range length as needed. Note that you can't callout
entire column though (D:D).
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"IanC" wrote:
I need to find the maximum value in column C where there is also data (a
date) in column D,E or F.
How do I find the row number for the maximum value in columns D-F? I need to
find the value of column C in this row.
Alternatively, how can I find the maximum value in column C in black
characters (other data appears in blue)?
Aternatively, how do I find the last row containing data in column D,E or F?
Many thanks.
--
Ian
--