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

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

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

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

Thanks All works great
"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

Hi Guys,

The initial function works but only with whole numbers, it doesn't seem to
work with % and also seems to round. Is there a way to change it to work
around this?

"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

Ev,
Just change all Longs to Singles. e.g.:

Function CalcMaxNeg(argRange As Range) As Long
Dim Cell As Range
Dim RunSum As Single
Dim MaxNeg As Single

.........etc

NickHK

"Ev" wrote in message
...
Hi Guys,

The initial function works but only with whole numbers, it doesn't seem to
work with % and also seems to round. Is there a way to change it to work
around this?

"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

Thanks, sorry for stupid question.
"NickHK" wrote in message
...
Ev,
Just change all Longs to Singles. e.g.:

Function CalcMaxNeg(argRange As Range) As Long
Dim Cell As Range
Dim RunSum As Single
Dim MaxNeg As Single

........etc

NickHK

"Ev" wrote in message
...
Hi Guys,

The initial function works but only with whole numbers, it doesn't seem
to
work with % and also seems to round. Is there a way to change it to work
around this?

"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