Running total that outputs 0 if the sum is negative, and resets running total on next row.

[drank]

Hi there,

I am trying to create a formula that calculates the running total of column A.

However, when the running total is negative, two things should happen:

1. The output should be zero.
2. The running total should start anew on the next row.

And most importantly... the formula cannot use column B as a reference.

Is this possible with one fluid formula? The formula would need to account for future values, so there may be multiple "resets".

Any help is appreciated!

[JBeaucaire]

The "not use column B" as a reference is non-sensical. The simplest way to do a running total is transactionally, adding the new transaction to the old "total". I've done it this way for years for non-negative running totals, the formula in B2, then copied down would be:

=MAX(0, A2+N(B1))

This gives the exact desired results.

[drank]

Yes, that does achieve the desired result (I actually used this exact formula when i first solved). However nonsensical the rules may be, they are there for a reason.

In this case, the means to the end is more important than the end itself. If you can find a way to replicate the goal within the guidelines, please post it here!

[AliGW]

You obviously have your reasons, but it would be helpful to know why column B must not be used in the formula.

OK - devil's advocate here:

1. In D2, type =B2 - copy down.
2. Hide column D.
3. In C2, type =MAX(0, A2+N(D1)) - copy down.

How's that?

[drank]

It's very close, but the sum doesn't seem to reset. It will result in a "0" for any negative values.

Here's what I show: 2,6,4,0,0,0,0,5,0,0,0

If it would help to know the reason for the formula being unable to reference itself, I am happy to explain! I am trying to solve this problem in a way that uses concepts which will be important later when using power pivot and power bi. In this problem, the formulas cannot reference themselves.

[AliGW]

OK - but the formula would not be referencing itself, so I'm still not clear. However, I'll see if I can think of another way.

[JBeaucaire]

It would be interesting as well to understand how Power Pivot is affected by the method the column B values use to calculate? All of my understanding of pivot tables is that they operate on the values in the source data, the underlying formulas irrelevant.

[AliGW]

Try this, please, in C2 drag copied down:

=IF(SUM(C1,A2)<0,0,SUM(C1,A2))

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.