1

I need to write a formula that will continue to sum up deficits and gains across a row in a running total when the total remains a negative number (not out of the deficit) but then gives a running total of "0" when that running total number becomes positive, and then if the running total number becomes negative again after that (numbers in subsequent columns), will start summing from there, discounting everything previously before the last "0" in the row.

For example, if person is -110 in the hole for first quarter, running total should read -110; then in Q2 gains 10, running total should read -100; then Q3 loses -40, running total should read -140; then in Q4 gains 150, running total should read 0; then in next fiscal year Q1 loses -30, running total should read -30.

So over the course of a couple of years, I need it to keep track of the deficit until the person climbs out of the deficit and is at 0 or above, then begin tracking that deficit again if they go negative. Each quarter, I need to send in everyone's deficits or lack thereof, and I would like to have it all formatted ahead of time so I can just plug in the number from the current quarter and have the running total be either 0 if they are not in a cumulative deficit or display the actual deficit if they do have a deficit.

Is this posisible?

Improve this question
1
  • 2
    please post some example data (in a tabular form, not embedded in text) with desired results to help us understand your situation (make sure to cover "ntil the person climbs out of the deficit and is at 0 or above, then begin tracking that deficit again if they go negative." too).
    – Máté Juhász
    May 22, 2019 at 19:00

1 Answer 1

Reset to default
1

If the quarterly numbers are in Row 1, starting in cell A1, then enter

  • A2 → =MIN(A1,0)
  • B2 → =MIN(A2+B1,0)

and select B2 and drag/fill to the right:

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .