Excel Formula to Subtract the Last Two Numeric Cells
Question details
Find the last two numeric cells in a column and subtract the earlier value from the later value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains payment values mixed with a title, blanks, or other nonnumeric entries.
- Observed behavior
- The desired result is the last numeric value minus the preceding numeric value, regardless of intervening nonnumeric cells.
Identify the column and the range that contains your data. The examples below use A2:A1000 and assume that at least two cells in that range contain numbers.
Use AGGREGATE to Find and Subtract the Last Two Numbers
Use this formula when numeric values are mixed with text, blank cells, or other nonnumeric entries.
AGGREGATE finds the two largest row numbers containing numeric values. INDEX retrieves those values, and the formula subtracts the earlier one from the later one.
Click an empty cell outside the data column, such as C2.
Type =INDEX($A:$A,AGGREGATE(14,6,1/ISNUMBER($A$2:$A$1000)*ROW($A$2:$A$1000),1))-INDEX($A:$A,AGGREGATE(14,6,1/ISNUMBER($A$2:$A$1000)*ROW($A$2:$A$1000),2)) and press Enter.
If your data extends beyond row 1000 or is in another column, edit both A2:A1000 ranges and both A:A references to match your data.

Subtract the Last Two Numbers in WPS Spreadsheets
Use a helper column in WPS Spreadsheets to identify numeric rows and subtract the last two values without counting text or blanks.
- 1. Open your spreadsheet: Open the workbook in WPS Spreadsheets and confirm that the values to check are in A2:A1000.
- 2. Create a helper column: In an empty cell B2, enter =IF(ISNUMBER(A2),ROW(),"") and press Enter.
- 3. Fill the helper formula: Select B2 and drag its fill handle down through B1000. Use an empty helper column so you do not overwrite existing data.
- 4. Calculate the difference: In an empty cell such as C2, enter =IF(COUNT(B2:B1000)<2,"Need at least two numeric values",INDEX(A:A,LARGE(B2:B1000,1))-INDEX(A:A,LARGE(B2:B1000,2))) and press Enter.

Frequently Asked Questions
Why not use COUNTA to find the last numeric cell?
COUNTA counts nonempty cells, including text, and its count does not necessarily match the last occupied row when blanks are present. Use ISNUMBER with AGGREGATE or a helper column to identify numeric rows instead.
What happens if the column has fewer than two numbers?
The AGGREGATE formula returns an error because it cannot find a second numeric row. Check the range for at least two numbers, or use the WPS helper-column formula, which displays a message instead.
Does the formula include numbers stored as text?
No. ISNUMBER treats numbers stored as text as nonnumeric. Convert those cells to numbers if they should be included in the calculation.
How do I include rows below row 1000?
Edit both occurrences of A2:A1000 in the AGGREGATE formula to cover the full data range, such as A2:A5000. In the helper-column method, fill the helper formula down to the same final row and update the B-column ranges.




