Fix Excel SCAN and LAMBDA #VALUE Errors with Empty Strings

A user on the Excel Tech Community forum recently posted a common frustration:

“I’m using a SCAN function to create a running total of a spilled array. The source array is generated by a FILTER that returns "" (empty strings) for certain rows to keep the layout clean. Even though these look empty, my SCAN returns a #VALUE! error the moment it hits one. Why doesn’t Excel just treat them as zero?”

This happens because while standard cell arithmetic often coerces "" to 0, the LAMBDA environment inside functions like SCAN, REDUCE, and MAP is stricter. If you attempt to add a number to a string (even an empty one), the calculation fails.

The Minimal Working Example

Tested on Microsoft 365 (Desktop and Web) as of October 2024.

If your data is in a spilled array starting at A2#, and you want a running total:

The Broken Formula:

=SCAN(0, A2#, LAMBDA(acc, val, acc + val))

The Fixed Formula:

=SCAN(0, A2#, LAMBDA(acc, val, acc + N(val)))

Why This Works

The N() function is a specialized tool that converts non-value items into numbers. In the context of SCAN:

1. Numbers remain unchanged.
2. Empty strings ("") or text are converted to 0.
3. Logical TRUE becomes 1, and FALSE becomes 0.

By wrapping your current value (val) in N(), you ensure that the accumulator (acc) is always performing math on two numbers, preventing the type-mismatch that triggers the #VALUE! error.

Step-by-Step Breakdown

If you are building a complex report, follow these steps to audit and fix your SCAN or REDUCE functions.

Step	Action	UI Path / Logic
1	Identify the Spilled Range	Click the top-left cell of your source data. Ensure it has the blue border indicating a “spilled” array (e.g., A2#).
2	Check for Nulls	Look for formulas that result in "" (like IF(A1=0, "", A1)). These are the culprits.
3	Insert the N() Function	Inside your LAMBDA block, wrap the variable representing the array value.
4	Apply IF Logic (Optional)	If you want the running total to “pause” on empty rows, use: IF(val="", "", acc + N(val)).

Adapting to Your Data

If you are dealing with more than just empty strings—such as actual text or errors—you might need a more robust “cleaning” step within your formula.

Handling Text and Empty Strings: If your spilled array contains actual words (like “Pending”) that should be treated as 0:

=SCAN(0, A2#, LAMBDA(acc, val, acc + IFERROR(VALUE(val), 0)))

Handling Nested LAMBDAs: If you are using SCAN inside another LAMBDA (e.g., within a BYROW or MAKEARRAY), the same rule applies. Always coerce your inputs to the expected data type before performing arithmetic.

Frequently Asked Questions

What if I need to apply this to filtered rows only? If you are using SCAN on a range that has been manually filtered using the Data > Filter tool, SCAN will still “see” the hidden rows. To ignore hidden rows, you must first wrap your source data in a FILTER function within the SCAN array argument: =SCAN(0, FILTER(A2:A10, SUBTOTAL(103, OFFSET(A2, SEQUENCE(ROWS(A2:A10),1,0), 0))), LAMBDA(acc, val, acc + N(val)))

Can I automate this with Power Query instead? Yes. If your dataset is very large (tens of thousands of rows), SCAN can occasionally cause calculation lag. In Power Query, you can add an Index Column and then a Custom Column using List.Sum(List.FirstN(#"Added Index"[Amount], [Index])). This is more performant for massive workbooks but isn’t “live” like a formula.

Why does my formula return #CALC! instead of #VALUE!? #CALC! usually indicates an empty array or an engine error, whereas #VALUE! specifically points to a data type mismatch. If you see #CALC!, check if your FILTER function providing the source data is returning any results at all.