Function Problems

Excel: Subtract the First Value from the Last Numeric Cell

Huma Ashraf ChHuma Ashraf Ch Nov 20, 2026 868 views

Question details

Calculate the difference between a starting value and the last numeric value in a column as new daily values are added.

Subtract the First Value from the Last Numeric Cell in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Daily numeric values are added to a column, and the user wants an automatically updated difference from the first value.
Observed behavior
The result should use the last numeric cell in the selected range and subtract the starting value.
Before you start

Identify the cell containing the starting value and the range where daily values will be added. The example below assumes the starting value is in B4 and searches B4:B10000; place the formula outside that range.

Solution 1Recommended

Use LOOKUP to subtract the starting value from the last number

Use this formula when the first value is in a known cell and later entries may include blanks or text.

1
Select a result cell

In Excel, select an empty cell outside B4:B10000, such as C4.

2
Enter the formula

Type =IF(ISNUMBER(B4),IFERROR(LOOKUP(2,1/ISNUMBER(B4:B10000),B4:B10000)-B4,""),"") and press Enter.

3
Adapt the references

Replace B4 with your starting-value cell and B4:B10000 with the range containing your daily values. Extend the range if entries will go beyond row 10000.

Use LOOKUP to subtract the starting value from the last number
How the formula handles entries: The formula skips blanks and text when finding the last number. It returns a blank if the starting cell is not numeric or no numeric value is found. Numbers stored as text are not counted.
Calculate in WPS Spreadsheet

Track the difference between daily values in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use the same LOOKUP formula to calculate the difference between a starting value and the last numeric entry.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
  2. 2. Choose a result cell: Select an empty cell outside the data range, such as C4 if your values are in B4:B10000.
  3. 3. Enter the formula: Type =IF(ISNUMBER(B4),IFERROR(LOOKUP(2,1/ISNUMBER(B4:B10000),B4:B10000)-B4,""),"") and press Enter.
  4. 4. Adjust and save: Change the cell references to match your data, then select File > Save or Save As to save the workbook.
Automatically includes new numeric entries within the selected rangeSkips blanks and text when finding the last numeric valueCompatible with Microsoft Excel XLSX and XLS workbooksUses familiar spreadsheet formulas and cell references
microsoft office alternative - wps office

Frequently Asked Questions

Will the result update when I add another daily value?

Yes. When you add a numeric value within B4:B10000, the formula uses the last numeric cell in that range. Extend the range in the formula if you add values below row 10000.

What happens if the last populated cell contains text?

The ISNUMBER test skips text and uses the last numeric cell instead. Numbers stored as text are also skipped.

Why does the formula return a blank?

The formula returns a blank if B4 is not numeric or if it cannot find a numeric value in the selected range. Check B4 and make sure your values are stored as numbers.

Can I use column G instead of column B?

Yes. If the starting value is in G4, replace B4 with G4 and B4:B10000 with G4:G10000. Keep the result formula outside the searched range.