Function Problems

Excel IF Formula for Different S and E Thresholds

Steve KSteve K Nov 18, 2026 869 views

Question details

Return N or Y based on the difference between P2 and H2, using a different threshold for S and E in N2.

Excel IF Formula for Different S and E Thresholds
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to compare P2 minus H2 against 14 when N2 is S and against 3 when N2 is E.
Observed behavior
The desired result is N when the applicable threshold is exceeded, Y when it is met or not exceeded, and a blank for other values in N2.
Before you start

Check that N2 contains the category and that P2 and H2 contain numeric values or valid Excel dates. Choose an empty result cell, such as Q2, so the formula does not overwrite your source data.

Solution 1Recommended

Use IF, OR, and AND to apply both thresholds

Use this formula when S and E each need a separate limit and other categories should return a blank.

1
Select the result cell

Click an empty cell in row 2, such as Q2.

2
Enter the formula

In the formula bar, enter =IF(OR(AND(N2="S",P2-H2>14),AND(N2="E",P2-H2>3)),"N",IF(OR(AND(N2="S",P2-H2<=14),AND(N2="E",P2-H2<=3)),"Y","")) and press Enter.

3
Apply it to other rows

Select Q2 and drag its fill handle down to copy the formula to the remaining rows.

Use IF, OR, and AND to apply both thresholds
Thresholds include equality: A difference of exactly 14 for S or exactly 3 for E returns Y. If N2 contains neither S nor E, the formula returns an empty string.
Spreadsheet formula solution

Apply S and E thresholds in WPS Spreadsheet

Use the same IF, OR, and AND formula in WPS Spreadsheet to evaluate each row against its applicable threshold.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to choose your Excel workbook.
  2. 2. Select a result cell: Click an empty cell in row 2, such as Q2.
  3. 3. Enter the formula: Enter =IF(OR(AND(N2="S",P2-H2>14),AND(N2="E",P2-H2>3)),"N",IF(OR(AND(N2="S",P2-H2<=14),AND(N2="E",P2-H2<=3)),"Y","")) in the formula bar and press Enter.
  4. 4. Fill down: Drag the fill handle from Q2 down the column to calculate results for other rows.
Calculate separate S and E thresholds in one formulaOpen and edit Microsoft Excel XLSX and XLS filesCopy the formula down a column with the fill handle
microsoft office alternative - wps office

Frequently Asked Questions

What happens when the difference equals the threshold?

The formula returns Y when P2-H2 equals 14 for S or 3 for E because the Y conditions use <=.

What does the formula return for a category other than S or E?

It returns an empty string, so the result cell appears blank.

Can I use this formula with dates?

Yes. If P2 and H2 contain valid Excel dates, P2-H2 gives their difference in days. Dates with time values can produce fractional days.

How do I change the thresholds?

Edit the formula in the formula bar and replace both occurrences of 14 for S or both occurrences of 3 for E, keeping the > and <= comparisons consistent.