Calculate running balance
IntermediateA running balance (also called a cumulative balance) shows your account balance after each transaction. This is essential for account reconciliation and tracking cash flow.
In this exercise, you have an account ledger with debits (money out) in column C and credits (money in) in column D. The opening balance is already provided in E2.
A running balance is calculated by starting with an opening balance and then adding credits and subtracting debits for each row. The key technique is using an expanding range with SUM: a range where the starting row is fixed but the ending row grows as you copy the formula down.
For example, =SUM(C$3:C3) sums from row 3 to the current row. When copied down to row 4, it becomes =SUM(C$3:C4). The dollar sign before the 3 locks the starting row.
Your task:
Calculate the running balance in column E for each transaction (cells E3 through E11). Each balance should reflect:
- The opening balance (E2)
- Plus all credits from row 3 up to the current row
- Minus all debits from row 3 up to the current row
Create a formula in E3 that you can copy down to fill the remaining cells.
Need some help?
Hint 1
The formula needs three parts: the opening balance reference, a SUM of credits, and a SUM of debits.
Hint 2
Use dollar signs to lock the starting row of your ranges. For example, D$3:D3 starts at row 3 and expands as you copy down.
Hint 3
Your formula should look like: =E$2+SUM(D$3:D3)-SUM(C$3:C3). The opening balance is fixed at E$2, and the SUM ranges expand as you copy the formula down.
Calculate running balance
IntermediateA running balance (also called a cumulative balance) shows your account balance after each transaction. This is essential for account reconciliation and tracking cash flow.
In this exercise, you have an account ledger with debits (money out) in column C and credits (money in) in column D. The opening balance is already provided in E2.
A running balance is calculated by starting with an opening balance and then adding credits and subtracting debits for each row. The key technique is using an expanding range with SUM: a range where the starting row is fixed but the ending row grows as you copy the formula down.
For example, =SUM(C$3:C3) sums from row 3 to the current row. When copied down to row 4, it becomes =SUM(C$3:C4). The dollar sign before the 3 locks the starting row.
Your task:
Calculate the running balance in column E for each transaction (cells E3 through E11). Each balance should reflect:
- The opening balance (E2)
- Plus all credits from row 3 up to the current row
- Minus all debits from row 3 up to the current row
Create a formula in E3 that you can copy down to fill the remaining cells.
Need some help?
Hint 1
The formula needs three parts: the opening balance reference, a SUM of credits, and a SUM of debits.
Hint 2
Use dollar signs to lock the starting row of your ranges. For example, D$3:D3 starts at row 3 and expands as you copy down.
Hint 3
Your formula should look like: =E$2+SUM(D$3:D3)-SUM(C$3:C3). The opening balance is fixed at E$2, and the SUM ranges expand as you copy the formula down.