Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use LAMBDA with SCAN for a running balance in Excel and Google Sheets
Topic:LAMBDA (reusable formulas)Excel & Google Sheets
=SCAN(0, B2:B7, LAMBDA(acc, x, acc + x))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Set up a two-column table: dates or labels in A2:A7 and transaction amounts (positive for credits, negative for debits) in B2:B7.
- 2In C2, enter =SCAN(0, B2:B7, LAMBDA(acc, x, acc + x)). The formula spills a running balance into C2:C7.
- 3The first argument (0) is the opening balance. Change it to your actual opening balance, e.g. 500.
- 4SCAN calls the LAMBDA for each element of B2:B7; acc holds the previous row's total, x holds the current transaction amount.
- 5The body acc + x accumulates. Replace this with any custom logic — for a running maximum use MAX(acc, x).
Tips
- SCAN spills results automatically in Excel 365 and Google Sheets; do not select a multi-cell range before entering the formula.
- To start the balance BEFORE the first row (i.e. show the opening balance in a separate cell), put your opening balance in a named range or a dedicated cell and reference it as the first SCAN argument.
- For a running count of a condition (e.g. count consecutive positive values), use SCAN(0, B2:B7, LAMBDA(acc, x, IF(x>0, acc+1, 0))).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can SCAN handle debits and credits that are in separate columns?
Combine them first with a helper array or MAP: =SCAN(0, MAP(B2:B7, C2:C7, LAMBDA(cr, db, cr - db)), LAMBDA(acc, x, acc + x)).
What happens if a cell in the range is blank?
Blank cells are treated as 0 in numeric SCAN formulas, which is usually correct for a running balance. Add an ISNUMBER check inside the LAMBDA if blanks should be handled differently.
Is SCAN available in all Excel versions?
No. SCAN requires Microsoft 365 or Excel 2024+. It is not available in Excel 2019 or earlier. Google Sheets supports it fully.
More on LAMBDA (reusable formulas)
See all →Reference
LAMBDA
Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.
How-toHow to write a recursive LAMBDA function
Build self-referencing recursive functions using LAMBDA with the Excel Name Manager or Google Sheets named functions — for tasks like factorial, Fibonacci, or tree traversal.
How-toHow to use LAMBDA with MAP across a range
Use MAP with a LAMBDA to apply a custom formula to every cell in a range in Excel 365 and Google Sheets, returning a transformed array without helper columns.
How-toHow to use LAMBDA with REDUCE for cumulative math
Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.
How-toHow to use BYROW and BYCOL to apply a LAMBDA per row or column
BYROW and BYCOL apply a LAMBDA to each row or column of a range in Excel 365 and Google Sheets, returning a per-row or per-column result without helper rows or columns.
How-toHow to use MAKEARRAY with LAMBDA
MAKEARRAY generates a 2-D array of any size in Excel 365 and Google Sheets by calling a LAMBDA with each row and column index, letting you build multiplication tables, calendars, or computed grids.