Method 2 – Simple but Better Method
Running totals
In this tutorial:
Step 1: Open tab named Method – Sales. With sales data already present in the first two columns A and B, in the third column (column C) where we will have cumulative sales, in cell C2 type in formula: =SUM($C$2:C2). This will give the starting value to cumulative balance.
Step 2: Fill the data down for the rest of rows by dragging down fill handler or double clicking it. Formula consists of a range with one part static and the other relative and when it is filled downwards the relative part will update automatically
Running Balance
Step 1: Open tab named Method 2 – Cash. With cash data already present in the first three columns A, B and C, in the fourth column (column D) where we will have balance of cash after each transaction, in cell D2 type in formula:Â =SUM($B$2:B2)-SUM($C$2:C2)
Step 2: Fill the rest of cells by dragging down the fill handle or double clicking it and formula will populate in rest of the cells.
Step 3: By default results are in positive and negative figures to have them in debit and credit as discussed before simply follow the steps learnt to do this via custom formatting in simple method’s Step 4 – Step 6 under running balance method.
Problems with Method 2
Although it solves the problem of #REF errors and now if in between rows are deleted the results don’t break and calculation still occur flawlessly for rest of the data. However, it does not solve our automation problem i.e. calculations are not carried out automatically on each new record input. To solve this issue we have our third method.
