Hi,

I have column of monthly investment returns. If the return for a
particular month is less than zero I want to regard the return for that
month as zero but then add the negative value to the return for the next
month that has a positive return. The pattern is random so there could
be any number of negative returns between two months with positive
returns.

Can some boffin please suggest a formula that will enable me to do
this?

Many thanks


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

=SUM(IF(E1:E10=0,-E2:E11,E2:E11),E1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"andrewc" wrote in
message ...

Hi,

I have column of monthly investment returns. If the return for a
particular month is less than zero I want to regard the return for that
month as zero but then add the negative value to the return for the next
month that has a positive return. The pattern is random so there could
be any number of negative returns between two months with positive
returns.

Can some boffin please suggest a formula that will enable me to do
this?

Many thanks


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4 would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column b
would continue.

Again, any help would be much appreciated!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Hi,

This is maybe not a good formula but it works.
Write it in b4, and drag it down.

=IF(A4<0;0;A4+IF(A3<0;IF(A2<0;A3+A2;A3)))

This will only cover 2 "bad" days in row.
You can make it cover more negative days if you want.

Best regards
Petter Bøhler

andrewc skrev:


Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4 would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column b
would continue.

Again, any help would be much appreciated!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Put this in B1

=IF(A1<0,0,A1)

Then in B2, add

=IF(A2<0,0,SUM(INDEX($A$1:A2,MAX(IF($A$1:A10,ROW( $A$1:A1)))+1):A2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy B2 down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"andrewc" wrote in
message ...

Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4 would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column b
would continue.

Again, any help would be much appreciated!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Bob,

That did the work. Much better then mine. :-)

Bob Phillips skrev:

Put this in B1

=IF(A1<0,0,A1)

Then in B2, add

=IF(A2<0,0,SUM(INDEX($A$1:A2,MAX(IF($A$1:A10,ROW( $A$1:A1)))+1):A2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy B2 down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"andrewc" wrote in
message ...

Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4 would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column b
would continue.

Again, any help would be much appreciated!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Wow that was complicated

put in B1

=IF(A1<0,0,SUM($A$1:A1))
and drag this formula down...


"Fingerjob" wrote:

Bob,

That did the work. Much better then mine. :-)

Bob Phillips skrev:

Put this in B1

=IF(A1<0,0,A1)

Then in B2, add

=IF(A2<0,0,SUM(INDEX($A$1:A2,MAX(IF($A$1:A10,ROW( $A$1:A1)))+1):A2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy B2 down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"andrewc" wrote in
message ...

Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4 would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column b
would continue.

Again, any help would be much appreciated!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Thank you all for your help!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=561034

Except that doesn't do what was asked by the OP. He said that he wanted the
sum ... between two months with positive returns ...

In other words, when a positive number is met, it adds all amounts after the
previous positive. Yours just adds irrespective.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny Lewis" wrote in message
...
Wow that was complicated

put in B1

=IF(A1<0,0,SUM($A$1:A1))
and drag this formula down...


"Fingerjob" wrote:

Bob,

That did the work. Much better then mine. :-)

Bob Phillips skrev:

Put this in B1

=IF(A1<0,0,A1)

Then in B2, add

=IF(A2<0,0,SUM(INDEX($A$1:A2,MAX(IF($A$1:A10,ROW( $A$1:A1)))+1):A2))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

and copy B2 down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"andrewc" wrote
in
message ...

Thanks Bob!

I can't adapt your formula for my purpose so I'm either being thick
or
didn't explain myself properly:

Column A
-0.22%
-0.80%
2.00%
3.63%
-1.00%
3.00%
5.00%

In cells b2 and b3 I would want a formula to return 0 (I want all
negative numbers in column a to be regarded as 0) while cell b4
would
contain the value 0.98% (ie the sum of a4 and any preceding negative
numbers since the last positive number). And so the series in column
b
would continue.

Again, any help would be much appreciated!


--
andrewc

------------------------------------------------------------------------
andrewc's Profile:
http://www.excelforum.com/member.php...o&userid=19613
View this thread:
http://www.excelforum.com/showthread...hreadid=561034

Hi Andrew,

If your desired output for this input:
-1%
-1%
1%
-2%
-2%
6%
-1%
-1%
7%
would be:
0%
0%
0%
0%
0%
1%
0%
0%
5%
then enter into cells B1:C2 (normal, NOT array-entered):
=MIN(0,A1) =MAX(0,A1)
=MIN(0,A2+B1) =MAX(0,A2+B1)
and copy B2:C2 down as far as necessary.

HTH,
Bernd

The best formula would be this one:

=IF(A2<=0;0;MAX(0;SUM($A$1:A2)-SUM(B$1:$B1)))

Put it in B2 and drag it down.

skrev:

Hi Andrew,

If your desired output for this input:
-1%
-1%
1%
-2%
-2%
6%
-1%
-1%
7%
would be:
0%
0%
0%
0%
0%
1%
0%
0%
5%
then enter into cells B1:C2 (normal, NOT array-entered):
=MIN(0,A1) =MAX(0,A1)
=MIN(0,A2+B1) =MAX(0,A2+B1)
and copy B2:C2 down as far as necessary.

HTH,
Bernd

Hello Fingerjob,

Sorry but I cannot agree he

1. Can we be sure that the OP wanted what we produced?

2. If yes: Which approach does the OP understand (better)?

3. Ok, your formula does not use a helper column but: It takes about
250 times more calculation time than my two formulas (tested with
FastExcel on 10,000 rows). In these cases I like to call the slower
approach a "runtime crime" :-)

SCNR,
Bernd

Hei again,

1. Pretty sure. :-). Any other way will not make any sense.
2. Impossible to say.
3. Agree with you, if you base the case that he would run this formula over
that many rows.

But i am sure that is not the case here. :-)

Best regards
Petter Bøhler

skrev:

Hello Fingerjob,

Sorry but I cannot agree he

1. Can we be sure that the OP wanted what we produced?

2. If yes: Which approach does the OP understand (better)?

3. Ok, your formula does not use a helper column but: It takes about
250 times more calculation time than my two formulas (tested with
FastExcel on 10,000 rows). In these cases I like to call the slower
approach a "runtime crime" :-)

SCNR,
Bernd

Forgot to tell why i think he will not have 10000 rows or even close.
The number hes trying to sort out is "monthly investment returns".


skrev:

Hello Fingerjob,

Sorry but I cannot agree he

1. Can we be sure that the OP wanted what we produced?

2. If yes: Which approach does the OP understand (better)?

3. Ok, your formula does not use a helper column but: It takes about
250 times more calculation time than my two formulas (tested with
FastExcel on 10,000 rows). In these cases I like to call the slower
approach a "runtime crime" :-)

SCNR,
Bernd

God dag Petter,

You are right: Speed does not seem to be the issue here.

But our formulas are different from all others' (including Bob's). So
let AndrewC decide which one he prefers to use.

Ha det,
Bernd