๐ง Logical Functions IF, AND, OR, IFERROR with Real-World Examples
Logical functions help Excel make decisions based on conditions.
๐ These functions are heavily used in:
Data Analysis
Finance
HR Reporting
Sales Dashboards
Business Rules
๐ฏ 1. IF Function
The IF function checks a condition and returns one value if TRUE and another if FALSE.
Syntax
=IF(condition,value_if_true,value_if_false)Example: Pass or Fail
Marks 75
Formula:
=IF(A2>=50,"Pass","Fail")Result: Pass
Business Example
Sales 120000
Formula:
=IF(A2>=100000,"Target Achieved","Target Missed")๐ 2. Nested IF Function
Used when multiple conditions need to be checked.
Example: Student Grades
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=50,"C","Fail")))Result
Marks Grade
95 A
80 B
60 C
40 Fail
๐ Useful for:
Employee ratings
Performance categories
Bonus calculations
๐ 3. AND Function
Returns TRUE only if ALL conditions are true.
Syntax
=AND(condition1,condition2)Example Student must pass both subjects.
=AND(A2>=50,B2>=50)Combined with IF
=IF(AND(A2>=50,B2>=50),"Pass","Fail")Example
Math Science Result
60 70 Pass
60 40 Fail
๐ 4. OR Function
Returns TRUE if ANY condition is true.
Syntax
=OR(condition1,condition2)Example
=OR(A2>=90,B2>=90)Combined with IF
=IF(OR(A2>=90,B2>=90),"Bonus Eligible","No Bonus")Example
Product A Product B Bonus
95 40 Eligible
60 70 Not Eligible
โ ๏ธ 5. IFERROR Function
One of the most important Excel functions. Used to handle errors gracefully.
Without IFERROR
=A2/B2If B2 is zero: #DIV/0!
With IFERROR
=IFERROR(A2/B2,"Invalid Data")Result: Invalid Data
๐ Makes reports cleaner and more professional.
๐ 6. Common Excel Errors
Error Meaning
DIV/0! Division by zero
N/A Value not found
VALUE! Wrong data type
REF! Invalid reference
NAME? Formula name error
Best Practice Wrap critical formulas with:
=IFERROR(formula,"Error")๐ฐ 7. Real-World Scenario: Sales Bonus
Rule Sales โฅ โน100,000 โ Bonus, Otherwise โ No Bonus
Formula:
=IF(B2>=100000,"Bonus","No Bonus")๐จโ๐ผ 8. Real-World Scenario: Employee Performance
Rule Score โฅ 90 โ Excellent, Score โฅ 75 โ Good, Score โฅ 50 โ Average, Else โ Needs Improvement
Formula:
=IF(B2>=90,"Excellent",IF(B2>=75,"Good",IF(B2>=50,"Average","Needs Improvement")))๐ฆ 9. Real-World Scenario: Loan Eligibility
Conditions Salary โฅ โน50,000, Experience โฅ 2 years
Formula:
=IF(AND(B2>=50000,C2>=2),"Eligible","Not Eligible")๐ 10. Real-World Scenario: Discount Eligibility
Conditions Purchase Amount > โน10,000 OR Premium Customer
Formula:
=IF(OR(B2>10000,C2="Yes"),"Discount","No Discount")๐ฏ Mini Practice Project
Create: Employee_Performance.xlsx
Data
Employee Score
Rahul 95
Priya 80
Amit 65
Neha 40
Tasks
โ Create Performance Rating
=IF(B2>=90,"Excellent",IF(B2>=75,"Good",IF(B2>=50,"Average","Poor")))โ Create Bonus Eligibility
=IF(B2>=80,"Bonus","No Bonus")โ Use IFERROR
=IFERROR(A2/B2,"Error")