Math & Trig
Calculate a running total (cumulative balance) that skips blank rows
Use an expanding SUM range for cumulative balances and suppress output on truly empty rows.
An expanding SUM avoids chaining each balance to the preceding balance
A balance based on the previous balance depends on that specific cell. Deleting the referenced cell or including a text header can break the chain.
SUM($B$2:B2), copied down, totals from the anchored start through the current row. This avoids chaining through earlier balance cells, but structural changes can still alter or invalidate references, especially when deleting the starting row. Check the formulas after inserts and deletes.
| Row | Entry (A) | Amount (B) | Expanding Range | Running Balance |
|---|---|---|---|---|
| 2 | Opening | 500 | $B$2:B2 | 500 |
| 3 | Sale | 240 | $B$2:B3 | 740 |
| 4 | Refund | -90 | $B$2:B4 | 650 |
| 5 | Sale | 350 | $B$2:B5 | 1000 |
Expanding running-total and blank-row guard
Wrapping SUM($B$2:B6) inside IF(ISBLANK(B6), "", ...) keeps pre-filled template rows visually blank until an amount is entered.
=SUM($B$2:B4)Blank-row check on pre-allocated rows
Row 6 has no amount yet, so IF(ISBLANK(B6), "", SUM($B$2:B6)) returns "".
ISBLANK excludes truly empty cells only; formulas returning empty text and spaces are still content. Use an explicit policy if those should also suppress the displayed balance.
=IF(ISBLANK(B6), "", SUM($B$2:B6))Try the example
Use the original values or change them to test your understanding.