Formula Errors

Use IF and OR in Excel to Calculate a Conditional Difference

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 870 views

Question details

Calculate C1 minus D1 only when B1 is less than A1 minus 2 or greater than 1; otherwise, display a blank.

Use IF and OR in Excel to Calculate a Conditional Difference
Product
Microsoft Excel
Device & OS
not provided
Scenario
A spreadsheet needs a conditional difference based on either of two comparisons involving B1.
Observed behavior
The formula should return the difference when at least one condition is true and display a blank when both are false.
Before you start

Enter numeric values in A1, B1, C1, and D1. Choose a separate cell for the result so the formula does not overwrite an input.

Solution 1Recommended

Combine IF and OR to subtract conditionally

Use this formula when either comparison should trigger the subtraction.

OR returns TRUE if B1 is less than A1 minus 2 or if B1 is greater than 1. IF then returns C1-D1; if neither comparison is true, it returns an empty string.

1
Select the result cell

In Excel, click an empty cell such as E1.

2
Enter the formula

Type =IF(OR(B1<A1-2,B1>1),C1-D1,"") and press Enter.

3
Check the result

Change the value in B1 to test each condition. E1 should display C1 minus D1 if either condition is true and appear blank otherwise.

Combine IF and OR to subtract conditionally
A displayed blank is not an empty cell: The formula returns an empty string ("") when both conditions are false. The cell looks blank but still contains a formula.
Calculate with WPS Spreadsheets

Calculate the same conditional difference in WPS Spreadsheets

Use IF and OR in WPS Spreadsheets to apply the same conditional subtraction and display a blank when neither condition is met.

  1. 1. Open the spreadsheet: Launch WPS Spreadsheets and open your workbook, or create a new spreadsheet.
  2. 2. Enter the values: Enter numeric values in cells A1, B1, C1, and D1.
  3. 3. Add the formula: Select E1, type =IF(OR(B1<A1-2,B1>1),C1-D1,"") and press Enter.
  4. 4. Save the workbook: Use File > Save As and choose XLSX if you need an Excel-compatible workbook.
Combine IF and OR for conditional subtractionOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet cells and formulas
QA img-9

Frequently Asked Questions

What happens if both conditions are true?

OR returns TRUE when either or both conditions are true, so the formula returns C1-D1.

Does the formula include values equal to A1 minus 2 or 1?

The comparisons are strict: B1<A1-2 excludes equality with A1-2, and B1>1 excludes equality with 1. The other condition can still make the formula return a difference.

How can I return zero instead of a blank?

Replace the empty string with 0: =IF(OR(B1<A1-2,B1>1),C1-D1,0).

Why does the result cell look empty but still contain a formula?

When both conditions are false, the formula returns an empty string. Select the result cell and check the formula bar to see the formula.