Formula Errors

Excel Formula to Subtract the Previous Nonblank Value

Ayan MasoodAyan Masood Nov 22, 2026 868 views

Question details

Create a formula that compares each value with the most recent nonblank value above it, skipping blank rows.

Subtract the Previous Nonblank Value in Excel
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.
Before you start

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.

Solution 1Recommended

Use LOOKUP to subtract the previous nonblank value

Use this formula when column A contains numbers and blank cells, including gaps of multiple rows.

1
Select the result cell

In Excel, select cell B2 next to the data in column A.

2
Enter the formula

In B2, enter =IF(A2<>"",IF(COUNT(A$1:A1),A2-LOOKUP(2,1/(A$1:A1<>""),A$1:A1),""),"") and press Enter.

3
Copy the formula down

Drag the fill handle from B2 down column B through the last row of data.

4
Check the result

For example, if A1 is 10, A2 is blank, and A3 is 15, B3 should show 5.

Use LOOKUP to subtract the previous nonblank value
Subtraction direction: The formula calculates the current value minus the previous value. To calculate the previous value minus the current value instead, replace A2-LOOKUP(2,1/(A$1:A1<>""),A$1:A1) with LOOKUP(2,1/(A$1:A1<>""),A$1:A1)-A2.
Calculate differences in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your Excel workbook.
  2. 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. 3. Fill down: Drag the fill handle from B2 down to the last row containing data in column A.
  4. 4. Save the workbook: Select File > Save As and choose XLSX if you want to keep the workbook in Excel format.
Calculate differences between numeric values separated by blank rowsOpen and save Microsoft Excel XLSX workbooksCopy formulas down a column with the fill handleUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.