IF, AND, OR and IFERROR
Learn how to make Excel formulas evaluate conditions, combine multiple tests, and handle formula errors. Practice logical functions using student marks and training institute reports.
1. IF Function
The IF function checks a condition and returns one value if the condition is TRUE and another value if it is FALSE. It is useful for pass/fail results, eligibility checks, attendance status, and sales targets.
=IF(logical_test, value_if_true, value_if_false)
Example: Student Pass or Fail
Suppose Samadhan's marks are stored in cell B2. The pass threshold for this example is 40 marks.
=IF(B2>=40,"Pass","Fail")
Diagram: IF evaluates a condition and chooses one of two results.
If B2 contains 85, Excel returns Pass. If B2 contains 32, Excel returns Fail.
2. AND Function
The AND function returns TRUE only when every supplied condition is TRUE. If even one condition is FALSE, AND returns FALSE.
=AND(condition1, condition2, ...)
Example: Course Eligibility
Samiksha is eligible for a sample training certificate when her marks are at least 40 and her attendance is at least 75%. Assume marks are in B2 and attendance is in C2.
=AND(B2>=40,C2>=75%)
The formula returns TRUE only if both conditions are met. To display a message, combine AND with IF:
=IF(AND(B2>=40,C2>=75%),"Eligible","Not Eligible")
3. OR Function
The OR function returns TRUE when at least one supplied condition is TRUE. It returns FALSE only when all supplied conditions are FALSE.
=OR(condition1, condition2, ...)
Example: Special Recognition
Omkar qualifies for a sample recognition list if he scores at least 90 marks in Excel or at least 90 marks in VBA. Assume the marks are stored in B2 and C2.
=IF(OR(B2>=90,C2>=90),"Recognize","Regular")
If B2 is 68 and C2 is 95, the result is Recognize because the second condition is TRUE. If both marks are below 90, the result is Regular.
4. IFERROR Function
IFERROR returns an alternative value when a formula produces an error. It helps make reports easier to read when calculations involve missing data, invalid references, or division by zero.
=IFERROR(value, value_if_error)
Example: Safe Division
Suppose D2 contains total marks and E2 contains the maximum marks. Dividing by zero can produce a #DIV/0! error.
=IFERROR(D2/E2,"Check maximum marks")
If the calculation succeeds, Excel returns the division result. If it produces an error, Excel displays Check maximum marks.
5. Comparison of IF, AND, OR and IFERROR
| Function | Purpose | Example |
|---|---|---|
| IF | Returns one result when true and another when false. | =IF(B2>=40,"Pass","Fail") |
| AND | Checks whether all conditions are true. | =AND(B2>=40,C2>=75%) |
| OR | Checks whether at least one condition is true. | =OR(B2>=90,C2>=90) |
| IFERROR | Returns an alternative value if a formula errors. | =IFERROR(D2/E2,"Check data") |
6. Step-by-Step Practical Exercise
Neha is preparing a training report with marks and attendance. She wants Excel to identify eligible students automatically.
| Student | Marks | Attendance | Eligibility |
|---|---|---|---|
| Samadhan | 85 | 90% | Eligible |
| Samiksha | 92 | 80% | Eligible |
| Pradnya | 76 | 70% | Not Eligible |
| Omkar | 32 | 85% | Not Eligible |
| Neha | 88 | 95% | Eligible |
- Enter student names in column A.
- Enter marks in column B and attendance percentages in column C.
- In D2, enter the eligibility formula shown below.
- Press Enter and copy the formula down the column.
- Change sample values to test different outcomes.
=IF(AND(B2>=40,C2>=75%),"Eligible","Not Eligible")
The example uses a minimum of 40 marks and 75% attendance. Adjust these thresholds according to the actual course rules.
7. Common Mistakes to Avoid
- Forgetting quotation marks around text results such as "Pass".
- Using AND when the requirement is that only one condition must be true.
- Using OR when every condition must be satisfied.
- Comparing percentages that have been entered as whole numbers incorrectly.
- Using IFERROR to hide problems without investigating their cause.
- Copying formulas without checking whether cell references adjust correctly.
Important Points to Remember
- IF chooses a result based on a condition.
- AND returns TRUE when all conditions are TRUE.
- OR returns TRUE when at least one condition is TRUE.
- IFERROR provides an alternative result when a formula errors.
- Test formulas using both successful and unsuccessful cases.
Summary
In this lesson, you learned how IF, AND, OR and IFERROR work. You practiced checking student marks, combining marks and attendance conditions, and displaying alternative messages when a formula produces an error.
These functions are useful for automating decisions and preparing reliable reports. In the next topic, we will learn about Excel Tables.