How to Subtract the Last Two Values Using Dynamic Array Formulas in Excel
Question details
The user needs an Excel dynamic array formula to identify the last two non-empty (or non-zero) values in a specific range, subtract them, or return the last value if they are identical.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating the difference between the last two recorded data points in a row while ignoring empty cells and zero values.
- Observed behavior
- The goal is to automatically extract the last two valid entries in a range like BD3:BO3 and compute their difference, or return the value itself if both entries are identical duplicates.
Ensure you are using a version of Excel that supports Dynamic Array formulas, such as Microsoft 365 or Excel 2021, as functions like LET, FILTER, and TAKE are required for this solution.
Use LET, FILTER, and TAKE to Subtract the Last Two Non-Zero Values
This approach uses modern dynamic array functions to filter out zeros and blanks, isolate the last two values, and perform the conditional subtraction.
The LET function allows you to assign names to calculation results, which makes complex formulas easier to read and faster to compute.
By combining FILTER to remove empty or zero cells and TAKE to extract elements from the end of the array, you can dynamically locate the last two numeric entries in your row.
Click on the cell where you want the calculation result to appear.
Type the formula `=IFERROR(LET(r, BD3:BO3, filled, FILTER(r, r>0), last, TAKE(filled, , -1), previous, TAKE(DROP(filled, , -1), , -1), IF(last=previous, last, previous-last)), "")` and press Enter.
If a zero is a valid value in your dataset and should be included rather than ignored, change `r>0` to `r<>""` in the FILTER section of the formula: `=IFERROR(LET(range, BD3:BO3, filled, FILTER(range, range<>""), last, TAKE(filled, , -1), previous, TAKE(DROP(filled, , -1), , -1), IF(last=previous, last, previous-last)), "")`.
Use WPS Spreadsheet for Advanced Array Formulas
WPS Office provides a powerful Spreadsheet application that fully supports advanced formulas, making it easy to perform complex data extraction and calculations just like in Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the data.
- 2. Input the dynamic formula: Select the target cell and paste the LET formula into the formula bar.
- 3. Calculate and format: Press Enter to evaluate the formula, then drag the fill handle down to apply it to other rows if necessary.
Frequently Asked Questions
What if my Excel version doesn't support the LET or TAKE functions?
If you are using an older version of Excel (like Excel 2016 or 2019), functions like LET and TAKE are not available. You would need to use a more complex combination of INDEX, MATCH, and AGGREGATE functions to find the last two non-blank values.
Why does the formula return a blank result?
The formula is wrapped in an IFERROR function that returns a blank string ("") if an error occurs. This usually happens if there are fewer than two valid numbers in the specified range.
How does the FILTER function ignore zero values here?
The condition r>0 inside FILTER(r, r>0) tells Excel to only keep numbers that are strictly greater than zero. This automatically excludes both blank cells (which Excel often evaluates as 0 in arrays) and actual 0 values.
Can I modify the formula to subtract the last three values instead?
Yes, but the logic would need to change. You would need to add another variable in the LET function using TAKE(DROP(filled, , -2), , -1) to extract the third-to-last value, and then adjust your final calculation accordingly.
