uneven rolling cumulative sum

[alexorfa]

Hi,

Ia have a sequence of numbers in Col A which reflect expenses (in -) and revenues (in +). I want a formula that will produce the outcome shown in Col B.
Basically I am looking for a formula that will calculate the rolling sum and "report" it only when it becomes positive, thereafter starting over from the the next row.

The attached file depicts the input in Col A and the manually entered output in Col B.

I thank you for any help offered.

Best
Alexander

 

[Mike86]

If you can use conditional formatting, it was fairly easy.

 

[alexorfa]

What formula would u use for conditional formatting?

 

[Mike86]

It's in the sheet. You just check if the cell is less than 0 and change the font to white. It's a cheap trick, but works.

 

[alexorfa]

It's in the sheet. You just check if the cell is less than 0 and change the font to white. It's a cheap trick, but works.

Thanks Mike86. I had not realized that you uploaded a sheet. I follow what you do and it is a clever trick. If I can't find a 'clean' solution I will use it. Unfortunately I have a complex sheet which already relies extensively on conditional formatting.

Thank again

 

[r2c2]

Interesting problem @alexorfa

I am sure someone will come and post a shorter and better solution. Until then, you can use this.

(Assuming the answers will be column C) use the array formula

=MAX(IF(SUM($C$2:C2)=0,SUM($A$3:A3), IF(AND(A3>0,C2>0),A3, SUM(INDEX($A$3:A3,MAX(ROW($A$3:A3)*($A$3:A3<0))-2):A3))),0)

See attached.

 

[bosco_yip]

.......... Unfortunately I have a complex sheet which already relies extensively on conditional formatting........

You can use Mike86's formula and adopt Custom format instead of Conditional formatting.

That is ,

in C3 enter formula :

=IF(C2>0,A3,C2+A3)

and >> Custom cells format, in the type box enter: 0;;0;

then copy down to C21

Regards
Bosco

 

[alexorfa]

Interesting problem @alexorfa

I am sure someone will come and post a shorter and better solution. Until then, you can use this.

(Assuming the answers will be column C) use the array formula

=MAX(IF(SUM($C$2:C2)=0,SUM($A$3:A3), IF(AND(A3>0,C2>0),A3, SUM(INDEX($A$3:A3,MAX(ROW($A$3:A3)*($A$3:A3<0))-2):A3))),0)

See attached.

Interesting problem @alexorfa

I am sure someone will come and post a shorter and better solution. Until then, you can use this.

(Assuming the answers will be column C) use the array formula

=MAX(IF(SUM($C$2:C2)=0,SUM($A$3:A3), IF(AND(A3>0,C2>0),A3, SUM(INDEX($A$3:A3,MAX(ROW($A$3:A3)*($A$3:A3<0))-2):A3))),0)

See attached.

Thank u r2c2 for your formula. Much appreciated. Let me share with you Eric W array formula from Mrexcel:
{=IFERROR(SQRT((SUM(OFFSET($A$1,MAX(IF($B$1:$B1<>"",ROW($B$1:$B1))),0):$A2)))^2,"")}
MAX(IF... is used to create a counter, SUM to post sums for each 'bracket' of the counter and SQRT & ^2 to get rid of negative entries.

Thanks again.

 

[alexorfa]

You can use Mike86's formula and adopt Custom format instead of Conditional formatting.

That is ,

in C3 enter formula :

=IF(C2>0,A3,C2+A3)

and >> Custom cells format, in the type box enter: 0;;0;

then copy down to C21

Regards
Bosco

Bosco, I now have two direct formula solutions for my problem but I like the idea of custom cell format to hide negative numbers. It works fine and I will definitely use it elsewhere. Can u explain the logic of 0;;0; or do I just use it as it is (...no questions asked)?
Thanks
Alexander

 

[alexorfa]

Bosco, I now have two direct formula solutions for my problem but I like the idea of custom cell format to hide negative numbers. It works fine and I will definitely use it elsewhere. Can u explain the logic of 0;;0; or do I just use it as it is (...no questions asked)?
Thanks
Alexander

Bosco,
I found the logic of 0;;0;
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
I knew you can format but not that you can hide content. Another tip learned. Thanks