Formula Errors

Excel Formula to Subtract the Last Two Numeric Cells

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Find the last two numeric cells in a column and subtract the earlier value from the later value.

Subtract the Last Two Numeric Cells in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select a result cell

Click an empty cell outside the data column, such as C2.

2
Enter the formula

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.

3
Adjust the range

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.

Use AGGREGATE to Find and Subtract the Last Two Numbers
Check the values: The formula requires at least two numeric cells. Numbers stored as text are ignored, while Excel dates count as numbers.
Work with spreadsheet formulas

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. 1. Open your spreadsheet: Open the workbook in WPS Spreadsheets and confirm that the values to check are in A2:A1000.
  2. 2. Create a helper column: In an empty cell B2, enter =IF(ISNUMBER(A2),ROW(),"") and press Enter.
  3. 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. 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.
Find the last two numeric values even when text or blanks appear between themOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a straightforward interfaceWork with spreadsheets using the free WPS Office suite
QA img-9

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.