Tricky Formula
 

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

Tricky Formula
 

=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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next positive number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

Good points!

Use this in A2 and copy down (still array entered)

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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

Good job.. wow

Improvements with the formula:

If you have 2 negative numbers in row and then a positive number that is
smaller then the two negative together and then a new positive number, then
you loose the negative difference betweeen the two first negative numbers and
the first positive number.

Eg.

-1 0
-2 0
2 0
3 3 (should be 2)

Best regards
Petter


Bob Phillips skrev:

Good points!

Use this in A2 and copy down (still array entered)

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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

Petter,

I think I am missing something here. Why should it be 2?

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Good job.. wow

Improvements with the formula:

If you have 2 negative numbers in row and then a positive number that is
smaller then the two negative together and then a new positive number,
then
you loose the negative difference betweeen the two first negative numbers
and
the first positive number.

Eg.

-1 0
-2 0
2 0
3 3 (should be 2)

Best regards
Petter


Bob Phillips skrev:

Good points!

Use this in A2 and copy down (still array entered)


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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

Hi bobby,

Because if you sum up the columns ( in my example) a and b you will get
different answers.
A = 2
B = 3

There is a toltal of - 3 after the 2 first rows, then a positiv number comes
(2) in row 3. If the posetive number is smaller then then negative numbers
together you will get a mismatch between the colums. There is still -1 that
will not been taked account for in colum b.

This means that if you have huge negative numbers followed by a tiny postive
number the hole negative effect will be gone in one zero.

exstrem case:

-5 0
-5 0
-5 0
-5 0
0,1 0
1 1 (do you think that 1 is the right number here?)

Best regards

Petter

(sorry about my poor english)

Bob Phillips skrev:

Petter,

I think I am missing something here. Why should it be 2?

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Good job.. wow

Improvements with the formula:

If you have 2 negative numbers in row and then a positive number that is
smaller then the two negative together and then a new positive number,
then
you loose the negative difference betweeen the two first negative numbers
and
the first positive number.

Eg.

-1 0
-2 0
2 0
3 3 (should be 2)

Best regards
Petter


Bob Phillips skrev:

Good points!

Use this in A2 and copy down (still array entered)


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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

As I understand the OP, that is exactly what he wanted. I don't think it is
a question of simply the sum, but more of the cumulative effect. I don't
think he ever wants a negative result, which is why your point about
previous negatives being greater than the current positive was so pertinent,
but I think that that is about it.

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi bobby,

Because if you sum up the columns ( in my example) a and b you will get
different answers.
A = 2
B = 3

There is a toltal of - 3 after the 2 first rows, then a positiv number
comes
(2) in row 3. If the posetive number is smaller then then negative numbers
together you will get a mismatch between the colums. There is still -1
that
will not been taked account for in colum b.

This means that if you have huge negative numbers followed by a tiny
postive
number the hole negative effect will be gone in one zero.

exstrem case:

-5 0
-5 0
-5 0
-5 0
0,1 0
1 1 (do you think that 1 is the right number here?)

Best regards

Petter

(sorry about my poor english)

Bob Phillips skrev:

Petter,

I think I am missing something here. Why should it be 2?

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Good job.. wow

Improvements with the formula:

If you have 2 negative numbers in row and then a positive number that
is
smaller then the two negative together and then a new positive number,
then
you loose the negative difference betweeen the two first negative
numbers
and
the first positive number.

Eg.

-1 0
-2 0
2 0
3 3 (should be 2)

Best regards
Petter


Bob Phillips skrev:

Good points!

Use this in A2 and copy down (still array entered)



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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next
positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

Hi bob,

You could be right,
but if he wanted it that way the numbers in colum b has no meaning at all.

Best regards.

Petter.

Bob Phillips skrev:

As I understand the OP, that is exactly what he wanted. I don't think it is
a question of simply the sum, but more of the cumulative effect. I don't
think he ever wants a negative result, which is why your point about
previous negatives being greater than the current positive was so pertinent,
but I think that that is about it.

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi bobby,

Because if you sum up the columns ( in my example) a and b you will get
different answers.
A = 2
B = 3

There is a toltal of - 3 after the 2 first rows, then a positiv number
comes
(2) in row 3. If the posetive number is smaller then then negative numbers
together you will get a mismatch between the colums. There is still -1
that
will not been taked account for in colum b.

This means that if you have huge negative numbers followed by a tiny
postive
number the hole negative effect will be gone in one zero.

exstrem case:

-5 0
-5 0
-5 0
-5 0
0,1 0
1 1 (do you think that 1 is the right number here?)

Best regards

Petter

(sorry about my poor english)

Bob Phillips skrev:

Petter,

I think I am missing something here. Why should it be 2?

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Good job.. wow

Improvements with the formula:

If you have 2 negative numbers in row and then a positive number that
is
smaller then the two negative together and then a new positive number,
then
you loose the negative difference betweeen the two first negative
numbers
and
the first positive number.

Eg.

-1 0
-2 0
2 0
3 3 (should be 2)

Best regards
Petter


Bob Phillips skrev:

Good points!

Use this in A2 and copy down (still array entered)



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

--
HTH

Bob Phillips

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

"Fingerjob" wrote in message
...
Hi again,

Improvements with the formula:
"IF(A2<0" should be "IF(A2<=0"
and
If the negative numbers sums up to be greater then the next
positive
number
it will show a negative number.
I dont know how to solve it. :-)

Bob Phillips skrev:

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

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

Tricky Formula
 

God dag tilbake.

Lets sum this up.

Bob is wrong
We know what AndrewC wants.

:-)

End of story.

Ha en strålende dag.(have a great day)

Regards
Petter


skrev:

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