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

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

=MAX(IF((D2:D20<"")+(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.

--
Biff
Microsoft Excel MVP


"IanC" wrote in message ...
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
--

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

"T. Valko" wrote in message
...
If I understand what you want, try this array formula** :

=MAX(IF((D2:D20<"")+(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.

--
Biff
Microsoft Excel MVP


"IanC" wrote in message
...
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
--

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"IanC" wrote in message ...
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

"T. Valko" wrote in message
...
If I understand what you want, try this array formula** :

=MAX(IF((D2:D20<"")+(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.

--
Biff
Microsoft Excel MVP


"IanC" wrote in message
...
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
--

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

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

"Luke M" wrote in message
...
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
--