Use array formula to calculate balance

[PaulHaz]

I have several spreadsheets dealing with money. They all have (at least) the same three columns: Amount In, Amount Out and Balance. If the are columns A, B and C, the columns headings are in row 1 and the opening balance is in C2, then C3 contains “=C2+A3-B3”. I can copy that down as far as I need, and it all works fine. Mostly.

If someone enters a value in the wrong column and then moves it, the formula calculating the balance on that row also changes, which is not what I want to happen. It changes even if I have protected the sheet and hence the balance column, which I think is arguably a bug - it really ought to ask if that is what I want it to do.

The obvious way to make a repeating formula unchangeable is to turn it into an array, something like {=C2:C102+A3:A103-B3:B103}, but that doesn’t work either as this gives me a 522 Error because most of the column C references are inside the array. Even though every instantiation of the array has no circular reference, the array as a whole thinks it does.

Firstly, is this a bug which can be fixed? The array ought to be shorthand for a series of identical calculations, and all the calculations, either individually or taken together, are valid, so the array ought to be valid.

Secondly, is there a workaround? This feels like a very common situation which should have been resolved in some way long ago, but maybe I’m searching for the wrong thing?

The other benefit of making this an array, or course, is that the spreadsheet ought to be smaller and, I assume, more efficient. But my primary concern right now is to stop people accidentally screwing it up. Telling people they are not allowed to cut and paste a number from the wrong place to the right place really isn’t an answer!

[schiavinatto]

Hello @PaulHaz, your file could be loaded with simple form, after filled in and confirmed the conference would be written to the file. The user would not have access to the worksheet that was saved.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

[PaulHaz]

Thanks for the comment, but the use case is one where the User repeatedly adds new entries, and needs to know the balance after each entry, at the point when it is entered.

[erAck]

If someone enters a value in the wrong column and then moves it, the formula calculating the balance on that row also changes, which is not what I want to happen. It changes even if I have protected the sheet and hence the balance column, which I think is arguably a bug - it really ought to ask if that is what I want it to do.

It’s not a bug, it’s how spreadsheets work and is expected.

Your case is solvable with named expressions using relative references. Place the cell cursor on C3 and create a named expression (either menu Sheet → Named Ranges and Expressions → Define…, or Ctrl+F3 and click Add). Choose a suitable name, e.g. calculate and as expression enter C2+A3-B3 then click the Add button. In cell C3 then enter =calculate, copy that cell to clipboard and paste to the target range, or pull the formula cell down.

{=C2:C102+A3:A103-B3:B103}, but that doesn’t work either as this gives me a 522 Error because most of the column C references are inside the array. Even though every instantiation of the array has no circular reference, the array as a whole thinks it does.

You make wrong assumptions about how things work. If you enter that formula in C3 then the calculation involves the range C2:C102 that clearly intersects C3.

The other benefit of making this an array, or course, is that the spreadsheet ought to be smaller and, I assume, more efficient.

No, it doesn’t. Size is about the same (the single cell approach internally shares one formula group as it is repeatedly the same), but efficiency for the array approach would be worse as for any change in the cell ranges given the entire array formulas would have to be recalculated, whereas (in this case) with the single cell approach recalculation only has to happen from the row on where a new value was entered. It doesn’t matter for 100 rows, but could for 100000 rows.

[PaulHaz]

I’m happy to argue my point about how arrays ought to function, but that should be done elsewhere.

I’m also happy to argue that there is little point in protecting a cell containing a calculation if it can be changed by someone without the password … but you have solved my problem with the named expression approach, so the question as stated has been answered.

[PaulHaz]

The suggestion to use a named expression solves my problem elegantly, so thank you!

[schiavinatto]

If the answer met your need, please click on the ball to the left of the answer, to finish the question.