Ask Your Question
0

Use array formula to calculate balance

asked 2019-07-13 23:50:54 +0200

PaulHaz gravatar image

updated 2019-07-14 02:11:06 +0200

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!

edit retag flag offensive close merge delete

Comments

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.

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 2019-07-14 02:07:48 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-07-15 16:38:51 +0200

erAck gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-13 23:50:54 +0200

Seen: 22 times

Last updated: 4 hours ago