Excel: Subtract the First Value from the Last Numeric Cell
Question details
Calculate the difference between a starting value and the last numeric value in a column as new daily values are added.

- 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.
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.
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.
In Excel, select an empty cell outside B4:B10000, such as C4.
Type =IF(ISNUMBER(B4),IFERROR(LOOKUP(2,1/ISNUMBER(B4:B10000),B4:B10000)-B4,""),"") and press Enter.
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.

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. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
- 2. Choose a result cell: Select an empty cell outside the data range, such as C4 if your values are in B4:B10000.
- 3. Enter the formula: Type =IF(ISNUMBER(B4),IFERROR(LOOKUP(2,1/ISNUMBER(B4:B10000),B4:B10000)-B4,""),"") and press Enter.
- 4. Adjust and save: Change the cell references to match your data, then select File > Save or Save As to save the workbook.

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.




