Excel Formula to Subtract the Previous Nonblank Value
Question details
Create a formula that compares each value with the most recent nonblank value above it, skipping blank rows.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains numbers separated by blank rows, and the user wants to calculate the difference between each number and the preceding filled value.
- Observed behavior
- Each result should show the current value minus the previous nonblank value; blank rows and values with no preceding number should return a blank.
Place the numeric values in column A, with any blank rows left empty, and reserve column B for the results. The formula starts in B2 and checks for earlier values beginning in A1.
Use LOOKUP to subtract the previous nonblank value
Use this formula when column A contains numbers and blank cells, including gaps of multiple rows.
In Excel, select cell B2 next to the data in column A.
In B2, enter =IF(A2<>"",IF(COUNT(A$1:A1),A2-LOOKUP(2,1/(A$1:A1<>""),A$1:A1),""),"") and press Enter.
Drag the fill handle from B2 down column B through the last row of data.
For example, if A1 is 10, A2 is blank, and A3 is 15, B3 should show 5.

Subtract the Previous Nonblank Value in WPS Spreadsheets
Open your worksheet in WPS Spreadsheets and use the same LOOKUP formula to calculate differences across blank rows.
- 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your Excel workbook.
- 2. Enter the formula: Select B2 and enter =IF(A2<>"",IF(COUNT(A$1:A1),A2-LOOKUP(2,1/(A$1:A1<>""),A$1:A1),""),""). Press Enter.
- 3. Fill down: Drag the fill handle from B2 down to the last row containing data in column A.
- 4. Save the workbook: Select File > Save As and choose XLSX if you want to keep the workbook in Excel format.

Frequently Asked Questions
How does the formula skip blank rows?
LOOKUP searches the earlier range in column A and returns its last nonblank value. The outer IF leaves the result blank when the current cell is empty.
Why is the result blank for the first number?
The COUNT check returns a blank when there is no earlier numeric value in the range above the current row.
Can I subtract the current value from the previous value instead?
Yes. In the formula in B2, change A2-LOOKUP(2,1/(A$1:A1<>""),A$1:A1) to LOOKUP(2,1/(A$1:A1<>""),A$1:A1)-A2, then fill the formula down.
Will the formula work if there are text entries among the numbers?
The formula is intended for numbers and blank cells. A text entry may be selected as the previous nonblank value and cause a subtraction error, so remove text entries from the data range before using it.




