Is there is an Excel formula for a ledger column that does running addition-subtraction w/out unique cell ids?

Question

For example, I know if column 5 is the balance and 3 and 4 are debit and credit, the formula in C5 would look something like = B5-C3+C4. But with this I have to have a unique formula in every cell, because the next row would be C5-D3+D4.

Is there some trick to doing a ledger so the column endlessly calculates the balance without having to copy and change a formula into all the cells?

I have searched on-line and haven't found a very straightforward answer.

Thanks!

Answer 1

Here is a slightly different way of keeping the ledger:



With the Debits (withdrawals) in column C, Credits (deposits) in column D and the Balance in column E enter this formula in E2 and copy down the length of column E as required. The balance will always be at the last entry. Until an entry is made in a row, that balance cell for the row will remain blank.



=IF(AND(C2="",D2=""),"", SUM($D$2:D2) - SUM(B$2:$C2))



This formula sums all the Credits and then subtracts the sum of all the Debits. This is a simple calculation because it only relys on the entries in two columns and not 3. It is also faster in calculation once you have many entries. For Example if you have 1000 rows of data, this formula will only have to perform 3 calculations to get the answer compared to about 3000 calculations using the method that you outline above.

Answer 2

I keep up with my checking account on Excel. In my file, the balance is in column G, with debits and credits in columns E and F. This is the formula I have in G3:



=IF(AND(E3="",F3=""),"",G2-E3+F3)



I copied this formula down a few hundred rows. G3 will remain blank until a debit or credit is entered in that row.



EDIT: to quickly copy down, move your cursor over the bottom right corner of cell G3. When the cursor changes to a solid plus sign, hold the left mouse button down and drag the formula down as far as needed.

Answer 3

No, only option is to do microsoft Excell. categorize your debit & credit info that way, and re-organize the files starting from D5-Ç3 to fit those FïLÈŠ.

Answer 4

in case you rather have purely 5 cells (or decrease than 8 after all), then this nested IF will artwork: =IF(A1<>fake, A1, IF(A2<>fake, A2, IF(A3<>fake, A3, IF(A4<>fake, A4, A5)))) in case you have extra effective than 8 (or particularly any quantity), then you may use this formula: =INDEX(A1:A100, MIN(IF(A1:A100<>fake, ROW(A1:A100)-ROW(A1)+one million, 999))) enter applying Ctrl-Shift-enter particularly of enter, as a results of fact this is an array formula. the way it works is it nicely-knownshows the row # of the 1st fee it is not fake, and then INDEXes to that cellular to get the fee. in case you decide on extra information, upload extra info or email. stable luck. .