Excel SCAN function
Like REDUCE, but returns every intermediate result: ideal for running balances and totals. It has the same name in Spanish Excel.
Syntax
English Excel
=SCAN(initial_value, array, lambda)
Spanish Excel (Mexico)
=SCAN(valor_inicial, matriz, lambda)
Spanish Excel (Spain, Latin America)
=SCAN(valor_inicial; matriz; lambda)
| Argument | What it is |
|---|---|
| initial_value | The value the accumulator starts with. |
| array | The range to walk through. |
| lambda | A LAMBDA(accumulator, value) returning the new accumulator. |
Example
REDUCE(start, range, LAMBDA(total, value, calculation)) walks the range and returns one result. SCAN does the same but shows every step: with account movements, it gives the balance after each one.
=SCAN(0,B2:B8,LAMBDA(saldo,mov,saldo+mov))
Common mistake
Excel 365 only. The result has the same shape as the range and spills down.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice SCAN
Type the formula in the sheet and check it instantly. Spanish or English function names both work. See all lessons
Running balance: SCAN and REDUCE
REDUCE(start, range, LAMBDA(total, value, calculation)) walks the range and returns one result. SCAN does the same but shows every step: with account movements, it gives the balance after each one.
In C2, with a single formula, calculate the balance after each movement using SCAN.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Movement | Balance | Deposits | |||
| 2 | Mar 01 | 15,000.00 | |||||
| 3 | Mar 03 | -2,400.00 | |||||
| 4 | Mar 07 | -860.00 | |||||
| 5 | Mar 10 | 5,200.00 | |||||
| 6 | Mar 15 | -7,300.00 | |||||
| 7 | Mar 22 | -1,150.00 | |||||
| 8 | Mar 28 | 3,900.00 | |||||
| 9 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is SCAN called in Spanish?
- It has the same name: SCAN. What changes is the separator: Spanish Excel in Spain and much of Latin America separates arguments with semicolons.
- Why does SCAN use semicolons in some Excel versions?
- It depends on regional settings. Where the comma is the decimal mark (Spain, much of South America), Excel separates arguments with semicolons. English Excel and Mexican Spanish Excel use commas.
- What is the most common SCAN mistake?
- Excel 365 only. The result has the same shape as the range and spills down.