Circular Dependency in Google Sheets

1
votes

I am trying to create the equivalent of a check register in Google Sheets for a joint account. The goal is to keep track of how much each person has in the account based on credits and debits, as well as an overall balance.

The columns I have set up are

  • A: Date,
  • B: Description,
  • C: Debit (person 1),
  • D: Credit (person 1),
  • E: Balance (person 1),
  • F: Debit (person 2),
  • G: Credit (person 2),
  • H: Balance (person 2),
  • and I: Balance (overall).

Where I am running into trouble is the formula for calculating the individual balances. The way I had it set up in Excel was to use a formula like this:

(In row H3) 

=H2-F3+G3

So this would take the balance of the person from row 2, add any credits and subtract any debits for Row 3, and insert that value into Cell H3. I have it set up to automatically copy the formula to each new row that is added, so I don't have to do it manually every time there is a new transaction.

The formula I currently have in that cell H3 in Google Sheets is:

=ARRAYFORMULA(H2:H-F3:F+G3:G)

Cell H2 has a static value, as it is my starting point for that column. The above formula is giving me a circular dependency error, and I cannot figure out why; I don't believe it is referencing cell H3 at all, but I just started working with Google Sheets, so maybe my understanding of the arrayformula is incorrect.

Any help on how to fix this would be greatly appreciated.

2
google-sheetsformulasarray-formulas

2 Answers

0
votes

Admittedly I've never used the ARRAYFORMULA function, but I believe the problem is the fact that your formula exists in the H column, but references the entire column H in what it is trying to compute (the H2:H part signifies that it acts on the whole column, but since the result is also stored in column H, it becomes a circular dependency).

It's perhaps not a great solution, but you could go back to the formula you had:

=H2-F3+G3

and simply 'drag' that formula all the way down your column.

If you have your heart set on the ARRAYFORMULA function, you'll have to store the result in a column other than one of the 3 used in the formula (G,F,H).

0
votes

Paste this formula in H3:

=ArrayFormula( SUMIF(ROW(F3:F),"<="&ROW(F3:F),G3:G)
              -SUMIF(ROW(F3:F),"<="&ROW(F3:F),F3:F)
              +H2)

The answer is about finding cumulative sum with ArrayFormula. Here's Adam's (@AdamL) sulution.