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 ?

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 ?

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 ?

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 ?

{=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 ?

Dear Biff
Many thanks for your help once again. Although the range is small at
present, a new row will be added daily and I want to process data from the
entire range, the last 10 entries, and the last 5 entries, all to be updated
daily.
I've seen previous posts from yourself on this subject that may also be
useful.
Regards
Graham

"T. Valko" wrote:

{=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 ?

Ok, I see why you want a dynamic range.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Graham" wrote in message
...
Dear Biff
Many thanks for your help once again. Although the range is small at
present, a new row will be added daily and I want to process data from the
entire range, the last 10 entries, and the last 5 entries, all to be
updated
daily.
I've seen previous posts from yourself on this subject that may also be
useful.
Regards
Graham

"T. Valko" wrote:

{=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 ?