Is there a way in Excel to reset cumulative total?

user_14509649 0
Viewed 641

I am trying to figure out a way to reset the cumulative sum every time I reach $600.

For example my customer pays me random sums every month, as soon as the total amount reaches 600, I would like to reset the counter and start counting the amount from $0 until the next cumulative sum of $600. If by month 6, my customer has paid $650, then I'd like to carry over the $50 to the next set of cumulative sum.

Here is the sample output

rolling-computation excel-formula modulo excel
3 Answers

You can use:

=(B3+D2)*(D2<= 600)+(B3+D2-600)*(D2> 600)

starting at third row.

enter image description here

user_121888610

Here is a 2-column solution which I feel will be better suitable to your needs than the more modest request implied in your question. The difference is in the handling of the situation when 600 has been reached. The extra column can be used to feed a credit column in the customer's account.

Here are the formulas.

[D2] =MOD(SUM(C$2:C2),600)
[E2] =INT(SUM($C$2:C2)/600)*600-SUM(E$1:E1)

Copy both formulas down to the end of column C. To suppress the display of zeroes apply this cell format to column E:- 0;;.

enter image description here

user_76754370

In C2, enter formula and copied down :

=B2+N(C1)-(N(C1)>600)*600

enter image description here

user_121251810
Related