{=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))}

Assuming there are no empty/blank cells *within* the range.

Array entered:

=MAX(IF(C38:INDEX(C38:C56,COUNT(C38:C56))<0,G38:IN DEX(G38:G56,COUNT(C38:C56))))

But, why use a dynamic range when the entire range is relatively small?


--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Hi, I'm trying to amend the array formula shown in my post below into a
dynamic array formula. I've got as far as the following :

{=MAX(IF(OFFSET(C38,0,0,$A$1-ROW(C38)+1,1)<0,(OFFSET(G38,0,0,$A$1-ROW(G38)+1,1))))}

But I'm getting an error message returned. Can anyone spot where I'm going
wrong ?
Many Thanks


"Graham" wrote:

Dear Biff
Many thanks for your help, looks to be working OK!! I'll now try to
modify
it to create a dynamic range.

=MAX(IF(G38:G56<0,G38:G56))

"T. Valko" wrote:

Try this array formula** :

=MAX(IF(A1:A3<0,B1:B3))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Hi, I'm trying to select the highest value in a column (which I can
do
with
=MAX), but only if the value in an adjacent column is a negative
number
e.g.
A B
1 10 18
2 -3 20
3 4 22

Although B3 is the highest value, I want to select B2 as it is the
next
highest with a negative value in the equivalent row, A2. Presumably I
need
some sort of condition applied to the MAX function ?