Tricky Formula

[andrewc]

Hi,

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

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

Many thank

 

[Bob Phillips]

=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]

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!

 

[Guest]

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:

 

[Bob Phillips]

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:A1>0,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)

 

[Guest]

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:A1>0,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)

 

[Guest]

Wow that was complicated

put in B1

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

Bob,

That did the work. Much better then mine.

Bob Phillips skrev:

 

[andrewc]

Thank you all for your help

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[bplumhoff]

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

 

[Guest]

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.

(e-mail address removed) skrev:

 

[bplumhoff]

Hello Fingerjob,

Sorry but I cannot agree here:

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

 

[Guest]

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

(e-mail address removed) skrev:

 

[Guest]

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".


(e-mail address removed) skrev: