Excel IF Function and Logical Tests

The IF function lets Excel make a decision: if something is true, show one result; otherwise show another.

The IF Function

=IF(logical_test, value_if_true, value_if_false)

• logical_test: a question with a yes/no answer, like B2>=50
• value_if_true: what to show if the answer is yes
• value_if_false: what to show if the answer is no

Comparison Operators

Pass or Fail

Students pass with 50 marks or more. In C2:

=IF(B2>=50, "Pass", "Fail")

IF with a Calculation

The result can also be a calculation. A shop gives 5% discount on bills of Rs 5000 or more:

=IF(B2>=5000, B2*5%, 0)

AND and OR

Use AND when all conditions must be true, and OR when at least one must be true.

A scholarship needs marks of 80 or more and attendance of 90% or more:

=IF(AND(B2>=80, C2>=90%), "Eligible", "Not eligible")

Free delivery is given if the bill is over Rs 3000 or the customer is a member:

=IF(OR(B2>3000, C2="Yes"), "Free", "Rs 200")

Nested IF

To choose between more than two results, put an IF inside another IF. Grades from marks:

=IF(B2>=80, "A", IF(B2>=70, "B", IF(B2>=60, "C", "F")))

Excel checks from left to right and stops at the first true test.

IFS: A Cleaner Way

In Excel 2019, Excel 2021 and Microsoft 365, IFS does the same thing more clearly:

=IFS(B2>=80, "A", B2>=70, "B", B2>=60, "C", TRUE, "F")

The last pair TRUE, "F" works as "everything else".

IFERROR

IFERROR shows something friendly instead of an error:

=IFERROR(B2/C2, 0)
=IFERROR(B2/C2, "No data")

Use IFERROR carefully. It hides all errors, including real mistakes in your formula.

Exercise