Thanks Nick,

But I can't get it to work,
I've inserted the code in a module and closed and re-opened excel but the
formula CalcMaxNeg() doesn't work and returns #Value


"NickHK" wrote in message
...
Ev,
How about this ?
Function CalcMaxNeg(argRange As Range) As Long
Dim Cell As Range
Dim RunSum As Long
Dim MaxNeg As Long

For Each Cell In Range("rngData").Cells
If Cell.Value < 0 Then
RunSum = RunSum + Cell.Value
Else
If RunSum < MaxNeg Then MaxNeg = RunSum
RunSum = 0
End If
Next

CalcMaxNeg = MaxNeg
End Function

NickHK

"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