Numbers SCAN: Running Totals, Balances and LAMBDA Results

• Introduced: The original introduction release and date are not established by the checked references. LAMBDA’s separately documented introduction does not establish this helper’s first release.
• Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
• Scope: Shared iWork reference. Function-specific historical Mac, iPad, iPhone and iCloud endpoints are unverified.
• Checked: October 14, 2026, against the official SCAN reference. Examples were independently calculated; no native Numbers test was performed.

SCAN(initial-value, array, LAMBDA(accumulator, value, calculation))

The official SCAN reference lists the initial accumulator value, the input array and the LAMBDA calculation. Inside LAMBDA, the accumulator is updated using the current array value. The required calculation goes last.

The signature does not mark the initial value optional or specify an omission default. This guide always supplies it. Apple’s examples return one intermediate result for each input; the initial seed is not inserted as an extra first result in those examples.

Create RunningBalance, with twelve columns A:L and eight rows, including one header row. Put Event, Change, Result index, SCAN balance, Recurrence, Difference and Prefix control in A1:G1. Enter the inputs below, and put 100 in H1 as the opening balance:

In D2 enter:

=INDEX(SCAN($H$1,$B$2:$B$5,LAMBDA(balance,change,balance+change)),C2,1)

Fill D2 through D5. INDEX selects positions one through four in the returned array. It does not use worksheet row numbers two through five. Apple’s INDEX entry allows a function-returned array as the first argument.

In E2 enter =$H$1+B2. In E3 enter =E2+B3 and fill E3 through E5. In F2 enter =D2-E2 and fill through F5. In G2 enter =$H$1+SUM($B$2:B2) and fill through G5.

All controls use the same event order and opening value. The results go outside B2:B5, so none of the original changes is overwritten.

Put Net movement in I1. In I2 enter =INDEX(SCAN(0,$B$2:$B$5,LAMBDA(total,change,total+change)),C2,1) and fill through I5 for 25,15,55,35.

In J2 enter =D2-I2 and fill through J5 for 100 in every row. Using zero changes the baseline without changing the movement sequence. It does not make the original balance calculation more correct; the two outputs answer different questions.

In D7 enter =REDUCE($H$1,B2:B5,LAMBDA(balance,change,balance+change)) for 135, and in E7 enter =D5-D7 for 0. This connects the final SCAN value to the corresponding REDUCE calculation.

Copy RunningBalance to ReorderedBalance. Replace A2:B5 with these event/change pairs: Payment B/-20, Receipt B/40, Payment A/-10, Receipt A/25. Leave C’s indices and the formulas unchanged.

The balances D, E and G become 80,120,110,135. The zero-seed movements I become -20,20,10,35. F remains zero and J remains 100; D7 remains 135 and E7 remains zero.

The closing balance is unchanged because the same four changes were added. The intermediate path is different: the reordered sequence first falls below the opening balance, while the original first rises. A final total alone cannot describe that timing. In a real ledger, preserve the correct chronology and keep each event label with its amount.

This control is an additive model. It does not establish that arbitrary order-sensitive LAMBDA formulas, rounding at each step or mixed-type arrays can be reordered safely.

Create VendorScanProduct with six columns A:F and four rows, using no header row. Enter 1,2,3 in A1:A3 and result indices 1,2,3 in C1:C3. In D1 enter =INDEX(SCAN(5,$A$1:$A$3,LAMBDA(accumulator,value,accumulator*value)),C1,1) and fill through D3 for 5,10,30.

For the separate two-dimensional illustration, create VendorScanGrid, with ten columns A:J and five rows, using no header. Enter 10,20,30 in A1:C1 and 40,50,60 in A2:C2. Put row indices 1,2 in E1:E2 and column indices 1,2,3 in F4:H4.

In F1 enter =INDEX(SCAN(10,$A$1:$C$2,LAMBDA(accumulator,value,accumulator+value)),$E1,F$4). Fill across through H1, then fill F1:H1 down to row 2. Expect 20,40,70 in F1:H1 and 110,160,220 in F2:H2, matching the actual source array. This example shows the displayed row-by-row accumulation for that grid; it is not a universal traversal test for every array type.

Apple documents the spilled-range operator # for referring to an array created by a formula. This guide extracts scalar results with INDEX so every destination is explicit. It does not establish a native spill layout or claim that spilling is unavailable.

Apple’s LAMBDA reference says definitions are local to their cell and LAMBDA functions cannot be nested. Each formula here contains one local LAMBDA definition; copying the formula copies it rather than creating a workbook-wide named function.

The examples use explicit numeric inputs. Mixed text, error cells, empty arrays, malformed parameters, array-valued intermediate results and exact runtime errors are outside the checked examples. Preserve the input/output separation and choose a calculation that matches the meaning and units of your data.

English formulas use decimal points and commas. Apple’s functions overview specifies semicolon argument separators when the decimal separator is a comma. These examples do not establish cross-client import or localized error behavior.

LAMBDA defines the step, INDEX extracts an intermediate result and SUM supplies the independent prefix controls.