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 ?