Excel IF Function

The IF function is one of the most important and frequently used functions in Excel. It allows a spreadsheet to make decisions based on conditions. Instead of calculating the same result for every row, the IF function can return different outcomes depending on whether a condition is true or false.

What is the IF Function?

The IF function tests a condition and returns one result if the condition is TRUE, and a different result if the condition is FALSE.

Think of it as asking a question: "Is this true? If yes, do this. If no, do that."

Syntax

  =IF(logical_test, value_if_true, value_if_false)

• logical_test: The condition to check. This is a comparison that results in TRUE or FALSE.
• value_if_true: What to return when the condition is TRUE.
• value_if_false: What to return when the condition is FALSE.

Comparison Operators Used in Logical Tests

• = Equal to
• <> Not equal to
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to

Basic IF Example

Example 1: Pass or Fail

  A student's score is in cell B2.
  Rule: If score is 50 or more → "Pass", otherwise → "Fail"

  Formula in C2:
  =IF(B2>=50, "Pass", "Fail")

  B2 = 75 → Result: "Pass"
  B2 = 42 → Result: "Fail"

Example 2: Bonus Calculation

  An employee's sales are in cell C2.
  Rule: If sales > 10000 → bonus is 500, otherwise → bonus is 0

  Formula in D2:
  =IF(C2>10000, 500, 0)

  C2 = 12000 → Result: 500
  C2 = 8000  → Result: 0

IF with Text Results

The result of an IF function can be text. Text values must be enclosed in double quotes.

Example

  Stock level in B3.
  Rule: If stock < 10 → "Reorder", else → "Sufficient"

  Formula: =IF(B3<10, "Reorder", "Sufficient")

  B3 = 5  → "Reorder"
  B3 = 25 → "Sufficient"

IF with Numeric Results

Numbers used as results do not need quotes.

Example

  Membership type in A2 (text: "Premium" or "Basic").
  Rule: Premium members get 20% discount; Basic members get 5%.

  Formula: =IF(A2="Premium", 20%, 5%)

  A2 = "Premium" → Result: 20%
  A2 = "Basic"   → Result: 5%

Nested IF (IF Inside an IF)

A nested IF places another IF inside the value_if_false argument. This allows checking multiple conditions in sequence.

Example: Grade Assignment

  Score in B2:
  90 or above → "A"
  80 to 89    → "B"
  70 to 79    → "C"
  Below 70    → "D"

  Formula:
  =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "D")))

  B2 = 92 → "A"
  B2 = 85 → "B"
  B2 = 74 → "C"
  B2 = 55 → "D"

Each nested IF is evaluated in order. If the first condition fails, the next IF is checked, and so on until a result is found.

IF with an Empty Value

Sometimes a cell should remain empty if a condition is not met. Use empty double quotes ("") for a blank result.

Example

  If profit in B5 is negative, display "Loss"; otherwise leave the cell blank.

  Formula: =IF(B5<0, "Loss", "")

  B5 = -200 → "Loss"
  B5 = 500  → (blank cell)

IF with Formula Results

The result of an IF can also be a formula or calculation rather than fixed text or a number.

Example

  Calculate tax only if income > 250000.
  Income in B6, tax rate 30%.

  Formula: =IF(B6>250000, B6*30%, 0)

  B6 = 300000 → 300000 × 30% = 90000
  B6 = 200000 → 0

IFS Function (Excel 2019 and Later)

The IFS function is an alternative to nested IFs. It checks multiple conditions without deeply nesting formulas, making it easier to read.

Syntax

  =IFS(condition1, result1, condition2, result2, ...)

Example: Grade with IFS

  =IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2<70, "D")

  → Easier to read than deeply nested IF formulas.

IFERROR Function

IFERROR handles errors in formulas gracefully. If a formula produces an error (like #DIV/0! or #N/A), IFERROR returns a custom message instead.

Syntax

  =IFERROR(formula, value_if_error)

Example

  =IFERROR(A2/B2, "Cannot divide by zero")

  If B2 = 0, instead of showing #DIV/0!, it displays: "Cannot divide by zero"

Summary

• The IF function tests a condition and returns different results based on whether it is TRUE or FALSE.
• Syntax: =IF(logical_test, value_if_true, value_if_false)
• Text results must be in double quotes; number results do not need quotes.
• Nested IFs check multiple conditions in sequence — each false result leads to the next condition.
• The IFS function (Excel 2019+) simplifies multiple conditions without deep nesting.
• IFERROR prevents error messages from displaying by replacing them with a custom value.