Conditional comparison of values.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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))
 
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
 
{=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:INDEX(G38:G56,COUNT(C38:C56))))

But, why use a dynamic range when the entire range is relatively small?
 
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
 
Back
Top