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