Sum the greatest run of negative numbers

[Ev]

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

[NickHK]

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" <evno@yahoo.com.au> wrote in message
news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
> 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]

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" <TungCheWah@Invalid.com> wrote in message
news:OrG9nCdlGHA.4868@TK2MSFTNGP04.phx.gbl...
> 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" <evno@yahoo.com.au> wrote in message
> news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>>
>
>

[Nigel]

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" <evno@yahoo.com.au> wrote in message
news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
>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]

Thanks All works great
"Ev" <evno@yahoo.com.au> wrote in message
news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
>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]

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" <evno@yahoo.com.au> wrote in message
news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
>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
>

[NickHK]

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" <evno@yahoo.com.au> wrote in message
news:uFnYpMllGHA.4144@TK2MSFTNGP05.phx.gbl...
> 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" <evno@yahoo.com.au> wrote in message
> news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
> >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]

Thanks, sorry for stupid question.
"NickHK" <TungCheWah@Invalid.com> wrote in message
news:%23B7pQamlGHA.2112@TK2MSFTNGP04.phx.gbl...
> 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" <evno@yahoo.com.au> wrote in message
> news:uFnYpMllGHA.4144@TK2MSFTNGP05.phx.gbl...
>> 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" <evno@yahoo.com.au> wrote in message
>> news:unzy%23uclGHA.3528@TK2MSFTNGP02.phx.gbl...
>> >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
>> >
>>
>>
>
>