Zoho Sheet SCAN Function: Running States and Array Results

Zoho lists SCAN among its six new LAMBDA helpers in the 15-function announcement, linked under March 2023 in its release timeline. This establishes the announcement month, not a numbered cloud build. The current SCAN reference was checked on October 15, 2026. The last functioning version is unknown; current documentation does not establish mobile or offline parity.

All expected results below were independently calculated from the stated inputs. They are reproducible reference expectations, not results captured from a native Zoho session.

SCAN([initial_value]; array; LAMBDA(accumulator; value; calculation))

The accumulator parameter receives the previous state, value receives the next input item, and calculation returns the updated state. Zoho documents an output array with the same size as the input array. For five input cells the result has five cells; the opening seed is not an extra sixth output row.

The initial value is marked optional, but its omitted default is not supplied in the checked reference. Every formula here specifies it. The examples use one vertical range, with the intended sequence shown top to bottom; no two-dimensional traversal rule is inferred from a similarly named function in another product.

Zoho’s dynamic-array announcement says to enter the formula in one cell: adjacent results spill automatically, without preselecting the result range or pressing Ctrl+Shift+Enter. Leave the intended output area clear. Existing data in that area can produce #SPILL!; inspect the obstruction before moving any valuable data.

Edit the formula at its starting cell rather than treating every result cell as an independent calculation. Keep input and output areas separate. These instructions concern the documented web-service behavior; they are not a native execution or an offline-client compatibility test.

Create InventoryScan. Put Change in A1 and Balance in C1. Enter 12,-5,0,8,3 in A2:A6. Put Opening in F1 and 100 in F2. Leave C2:C6 empty, then enter only in C2:

=SCAN(F2;A2:A6;LAMBDA(a;b;a+b))

The first output is 112, because the first change has already been applied to the opening 100. The zero change leaves the state unchanged. The last output is the closing balance; the preceding values remain useful for finding when a discrepancy began.

In a copy change A3 from -5 to -7. C2 remains 112, while C3:C6 become 105,105,113,116. An input correction affects that row and later states, rather than every row equally. Changing the opening value F2 to 200 with the original inputs instead shifts all five outputs upward by 100.

On a separate sheet put 1,3,5,7,9 in A2:A6. Enter =SCAN(1;A2:A6;LAMBDA(a;b;a*b)) in C2, leaving C2:C6 clear. The expected cumulative products are 1,3,15,105,945.

This uses the explicit odd-number product pattern in Zoho’s own example, with all input cells supplied. Starting the same product with zero gives 0,0,0,0,0. If a later factor is zero, all later cumulative products remain zero too. These are properties of multiplication, not evidence that SCAN ignores blank or erroneous cells.

Create IndexPath with changes 0.10,-0.05,0.02 in A2:A4 and an initial index level of 100 in F2. Enter in C2:

=SCAN(F2;A2:A4;LAMBDA(a;b;a*(1+b)))

The expected levels are 110,104.5,106.59. Format the changes as percentages if useful, but store 10% as 0.10 rather than 10. The final level represents a 6.59% overall change from 100, not the 7% obtained by adding the three percentage changes.

For this pure multiplication model, changing the order of factors preserves the mathematical endpoint but changes intermediate levels. The illustration is a supplied arithmetic index, not a prediction, investment recommendation or model of costs, contributions and withdrawals.

On another sheet put observations 12,8,14,6 in A2:A5. Put the initial smoothed value 10 in F2. In C2 enter =SCAN(F2;A2:A5;LAMBDA(a;b;(a+b)/2)).

The expected path is 11,9.5,11.75,8.875. For example the third update is (9.5+14)/2=11.75. This is a recurrence giving the previous state and current observation equal weight; it is not a running arithmetic mean of all observations seen so far.

Change only the last observation from 6 to 10. The first three states stay unchanged and the last becomes 10.875. This is a useful test that the formula uses the previous accumulated state, rather than repeatedly mixing every observation with the original seed.

The SCAN entry names #NUM! for an array that is too large but does not quantify the limit. Shared name/type/reference errors are also listed. The general array documentation separately establishes obstruction-related #SPILL!. Blank handling, embedded error propagation, invalid LAMBDA arity and empty-array behavior remain unverified.

If output is unexpectedly reduced to a single value, check whether an implicit-intersection operator is being applied; Zoho’s array announcement describes @ as suppressing array output. If the first displayed balance equals only the seed, revisit the expected update sequence. If the result area conflicts with input cells, move the intended output to a separate clear area before entering the formula.

Keep the trace beside the observations when auditing a recurrence. Checking only the last cell can miss an incorrect intermediate path that happens to reach the same endpoint. The independent calculations in this guide verify the supplied recurrences, not every native array or coercion behavior.