Turning negative results to 0 in an ongoing sum

[ZandiD]

Hello everyone,

I have a tricky question that I can't find the answer to no matter how hard I google.

I have a long list of numbers, and I need to summarize them in such a way that whenever it dips below 0, it gets treated as zero.

For example, if the list is

10
-20
10

then the result should be 10, rather than 0

As the list is quite long, if(if(A1+A2<0, "0",A1+A2)+A3... wouldn't work unfortunately.

Thanks a lot
Zandi

[Fotis1991]

Try

Please Login or Register  to view this content.

But the result should be 20. Not 10. Correct?

[Chris 53]

Try =Sumif(A1:A3,">0")

Chris

[ZandiD]

Hey guys,

Thanks for the proposed solutions.

Unfortunately it's not that simple, the result should be 10

The way it shouldwork is that whenever a step in the calculation goes below 0, it is treated as 0.

So in my example you have

((10 - 20) + 10)

Because 10 - 20 is -10, that should be treated as zero, and the final result is then 10

I found a roundabout solution like so:

10	=A1
-20	=IF(B1+A2<0,0,A2+B1)
10	=IF(B2+A3<0,0,A3+B2)

Would be happy to hear there's a more elegant solution, but if not, this will do

[zbor]

Here, try this in B2. Data must start from A2 (first row must be empty).

Please Login or Register  to view this content.

and extend down

[ZandiD]

Brilliant! Thanks a lot