Function negmax(xRng As Range) As Long
Dim xCell As Range
Dim xSum As Long
xSum = 0: negmax = 0
For Each xCell In xRng
If xCell.Value < 0 Then
xSum = xSum + xCell.Value
Else
xSum = 0
End If
Debug.Print xSum, negmax
If xSum < negmax Then negmax = xSum
Next
End Function

You call the function using

negmax(Range("A1:A14"))


--
Cheers
Nigel



"Ev" wrote in message
...
I have a list of returns like that listed below, I want to find out the
greatest continuous run of negative returns,
(i.e. -24-3 =-27, and is greater than -2-4-3-1-1-1=-12) so it would return
a result of -27
6
7
2
0
-2
-4
-3
-1
-1
-1
7
-24
-3
2
4

A function would be the most preferable solution