How to Use the SCAN Function with Irregular Arrays in Excel
Question details
The user needs to calculate cumulative growth rates using the SCAN function when the provided value and rate arrays have irregular shapes or contain blank cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating cumulative growth rates across inconsistently structured or jagged datasets where some periods have no rate changes.
- Observed behavior
- The goal is to calculate sequential cumulative values while properly ignoring blank cells (treating them as zero-percent increase) and optionally formatting unchanged results.
Ensure your spreadsheet application supports Dynamic Array formulas, as functions like SCAN, MAP, and LAMBDA are required for advanced array evaluations.
Use SCAN and LAMBDA to Handle Blank Rate Areas
Use this core method to apply growth rates cumulatively while preserving the previous value if the current rate cell is blank or omitted.
By nesting ISOMITTED inside the LAMBDA function, you can instruct the SCAN formula to treat blank areas as a zero-percent increase, maintaining the cumulative total without throwing an error.
Click on the empty cell where you want the upper-left corner of your cumulative results to spill.
In the formula bar, type the following formula: =SCAN(value, rates, LAMBDA(val, rate, IF(ISOMITTED(rate), val, val*(1+rate)))). Replace 'value' with your starting value or array, and 'rates' with your array of growth rates.
Press Enter. The results will dynamically spill into the adjacent cells, carrying over the previous value whenever it encounters a blank cell in the rates array.
Format Unchanged Values Using MAP
Wrap your SCAN formula in a MAP function to visually distinguish unchanged values by displaying hyphens.
Handle Complex Jagged Arrays Using Thunks
For heavily irregular arrays that traditional dynamic formulas cannot evaluate natively, use helper functions to build custom arrays.
Process Dynamic Arrays Effortlessly with WPS Office
WPS Office fully supports modern Dynamic Array functions, including SCAN, LAMBDA, and MAP. You can easily calculate complex cumulative growth rates across irregular arrays with the exact same formulas, ensuring high efficiency and perfect compatibility.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the irregular data arrays.
- 2. Select the Target Range: Click on the cell where you wish the dynamic array results to begin spilling.
- 3. Input the SCAN Formula: Click the formula bar, paste your SCAN and LAMBDA formula, and adjust the cell references.
- 4. Press Enter to Spill: Hit Enter. WPS Spreadsheet will automatically calculate and spill the dynamic array onto your sheet.
Frequently Asked Questions
Why does my SCAN formula return a #NAME? error?
A #NAME? error usually indicates that your version of the spreadsheet software does not support Dynamic Array functions. Ensure you are using a modern version, such as the latest WPS Office or Microsoft 365, which fully supports SCAN and LAMBDA.
What does the ISOMITTED function do in this formula?
The ISOMITTED function checks if a specific argument (like the rate from a blank cell) is missing or skipped. In the LAMBDA formula, it allows the calculation to gracefully ignore the blank cell and carry forward the previous value instead of generating a calculation error.
How do I fill empty spaces in a jagged array with zeros?
You can use the EXPAND function to resize an array to specific row and column dimensions. By setting the 'pad_with' argument of the EXPAND function to 0, all previously empty spaces will be populated with zeros.
What is a 'thunk' in Excel arrays?
A 'thunk' is a programming term applied to Excel for a LAMBDA function that takes no arguments, e.g., LAMBDA()(calculation). It is used to delay the evaluation of a formula until it is specifically called, allowing users to pack complex arrays or irregular data shapes into single cells for later processing via REDUCE or MAP.
