Conditional MAX

[IanC]

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.

 

[T. Valko]

If I understand what you want, try this array formula** :

=MAX(IF((D220<>"")+(E2:E20<>"")+(F2:F20<>""),C2:C20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Luke M]

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(D2100)+ISNUMBER(E2:E100)+ISNUMBER(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),D2100,0)),MATCH(MAX(D2:E100),E2:E100,0),MATCH(MAX(D2:E100),D2100,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(D2100)+ISNUMBER(E2:E100)+ISNUMBER(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).

 

[IanC]

You understand correctly, Biff

As the formula needs to be on another sheet, I had to amend it slightly, but
the following works perfectly.

=MAX(IF((Rooms!$D3:$D10000<>"")+(Rooms!$E3:$E10000<>"")+(Rooms!$F3:$F10000<>""),Rooms!$C3:$C10000))

Many thanks.

Ian

 

[IanC]

Thanks Luke. I ended up using Biff's suggestion (T.Valko).

I quickly tried your suggestions to see how they worked with the following
results.

The first formula returns #VALUE!. I haven't figured out why.
The second returns 6.01 as expected. You commented on the formula expecting
only one occurence of the maximum value. This isn't the case as D, E and/or
F could contain the same date. The date/row combination WILL be unique, so
it's not an issue as the formula returns the row for the first instance of
the max.
The third formula would not enter as an array, but entered normally it
returns 44, which is the row containing the max date as expected.

Many thanks.

Ian

 

[T. Valko]

You're welcome. Thanks for the feedback!