digest
Short Summary
Main recommendation: Use a single dynamic spilled array formula with the SCAN function to calculate a running balance for bank reconciliation, rather than copying single-cell formulas down a column. The formula lives only in the top cell and spills automatically, so editing requires changing just one cell. The video applies this to both a book balance and a bank balance, the latter filtered by a "Y" in a reconcile column.
Key Points
- Aggregate functions such as SUM, MAX, MIN, and AVERAGE cannot spill a dynamic array because they return a single answer, so SCAN is used instead.
- SCAN produces a running total by carrying the previous result forward, which replaces the "cell above" reference used in single input, single output formulas.
- Subtracting the entire Out column from the entire In column first converts two columns into one, which SCAN can then accumulate.
- Cells below the top cell contain "ghost" formulas that are part of the spill; they look empty but can still be referenced by other formulas.
- For the bank balance, an IF function with ISNUMBER (book balance) or a reconcile-column check (bank balance) returns FALSE for excluded rows, which SUM ignores.
Details Worth Noting
- Concrete instructions:
- Book balance: in the top cell, enter SCAN with initial value 0, array argument the In column minus the Out column, and SUM as the function.
- To hide numbers in rows without a date, wrap the formula in an IF with ISNUMBER on the date column, showing the result if true and a zero-length text string if false.
- Bank balance: add an IF inside the array, IF(reconcile_column = "Y", amount_array), omitting value_if_false so FALSE is returned for unreconciled rows; SUM then skips them.
- To edit, change only the top-cell formula and press Control+Enter; the spill updates automatically without recopying.
- To delete the spill, delete only the top cell, since the whole formula lives there.
- The video explains that in the single-cell formula, the initial value of zero works because the first row adds to zero, and each later row adds to the prior running total.
- The video notes that ISNUMBER returns TRUE for numeric values and FALSE otherwise, which is how spilled numbers are filtered.
- The zero-length text string ("") is presented as the Excel syntax for displaying nothing in a formula result.
- The video frames the difference between the two balances: the book balance must account for every entry, while the bank balance only includes items marked "Y" as cleared.
Bottom Line
Once a running total is built as one SCAN formula over a single combined column, reconciliation logic can be maintained in one cell instead of across many copied formulas.
