How to Compare Excel Rows with Previous Values Using SCAN
Question details
The user needs a way to evaluate and compare values in current Excel rows against previous rows using dynamic array functions (SCAN, VSTACK, DROP, MAP) to identify new maximum values and compute running totals.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Identifying new maximum values dynamically or calculating running commission totals without relying on traditional drag-down cell formulas.
- Observed behavior
- By manipulating arrays—specifically shifting an array down by adding a zero at the top and removing the last row—users can force Excel to compare a row's current value directly with the previous row's running result.
Ensure you are using a modern spreadsheet environment (such as Microsoft 365 or the latest WPS Office) that supports dynamic array functions, as older versions do not include SCAN, VSTACK, or DROP.
Use SCAN, VSTACK, and DROP to Identify New Maximums
This is the recommended method for creating a running calculation and comparing it against the previous row's value using an array offset.
The core concept relies on array cloning and shifting. First, you calculate a running maximum using the SCAN function. Then, you shift that newly created array down by one position using VSTACK (to put a 0 at the top) and DROP (to remove the final excess row). This aligns the previous row's maximum with the current row, allowing a direct mathematical comparison inside an HSTACK.
Click on an empty cell where you want the dynamic array result to spill. Ensure there is enough empty space below it to accommodate the entire dataset length.
Type the formula: =LET(currentMax, SCAN(0, currentValue, MAX), priorMax, DROP(VSTACK(0, currentMax), -1), isNewMax, currentMax>priorMax, HSTACK(currentMax, IF(isNewMax, "New Max!", "Same old")))
Replace 'currentValue' in the formula with your actual data range (e.g., A2:A20).
Press Enter. The formula will automatically spill down the column, outputting both the running maximum and a text label indicating if a new maximum was reached.
Calculate Running Commission Totals with MAP and VSTACK
Adapt the array-shifting logic to cumulative sums in order to isolate the incremental changes, such as calculating line-by-line commissions payable.
Easily Manage Dynamic Arrays with WPS Office
WPS Spreadsheet provides comprehensive support for modern dynamic array functions. You can easily build complex formulas using SCAN, VSTACK, DROP, and LET to completely automate your data analysis without traditional drag-down limits.
- 1. Download and Install: Download WPS Office Free, install it on your device, and open your existing Excel workbook.
- 2. Enter Array Formulas: Select any empty cell, type your LET, SCAN, or VSTACK formulas exactly as you would in modern Excel.
- 3. Enjoy Automatic Spilling: Press Enter, and WPS Spreadsheet will automatically execute the array logic, spilling the results into adjacent cells instantly.
Frequently Asked Questions
Why does my SCAN function return a #NAME? error?
This usually happens if your spreadsheet software is not updated. SCAN, VSTACK, and DROP are modern dynamic array functions. Ensure you are using Microsoft 365, Excel 2021, or the latest version of WPS Office.
How do VSTACK and DROP help compare previous rows?
By stacking a zero or blank value at the top of an array using VSTACK, and then dropping the last row using DROP(-1), you effectively push all values down by one row. This creates a 'previous value' array that aligns perfectly with your original array for easy row-by-row comparison.
Can I use this dynamic array approach for calculating running inventory?
Yes. By replacing the MAX logic inside the SCAN function with a cumulative sum or subtraction, you can track running inventory totals and seamlessly compare current stock levels with previous rows.
Do I need to copy or drag the formula down the column?
No. Because these are dynamic array functions, you only need to enter the formula in the top-left cell of your destination range. The results will automatically 'spill' down the column to exactly match the dimensions of your input data.
