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))).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.