AI Reading
Quick summary of this article
Logical functions help Excel make decisions based on conditions. They allow you to classify records, calculate incentives, assign grades, check eligibility, identify performance levels and display different results depending on whether a condition is true or false.
In this chapter, you will learn how to use IF, nested IF, IFS, AND, OR, NOT, IFERROR and IFNA. Every function is explained through practical employee, student, sales and business-reporting examples.
Learning Objectives
After completing this chapter, you should be able to:
- Understand TRUE and FALSE conditions in Excel.
- Use the IF function to perform conditional calculations.
- Use nested IF and IFS for multiple conditions.
- Combine IF with AND and OR.
- Reverse logical conditions using NOT.
- Handle formula errors using IFERROR and IFNA.
- Create grading, incentive and eligibility systems.
- Write readable and maintainable logical formulas.
1. Understanding Logical Tests in Excel
A logical test compares two values and returns either TRUE or FALSE. Logical tests form the foundation of conditional formulas.
Common Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | =A2=100 |
> |
Greater than | =A2>100 |
< |
Less than | =A2<100 |
>= |
Greater than or equal to | =A2>=100 |
<= |
Less than or equal to | =A2<=100 |
<> |
Not equal to | =A2<>100 |
Basic Logical-Test Examples
=A2>=50
=B2="Completed"
=C2<>"Inactive"
=D2=TODAY()
Each formula returns either TRUE or FALSE. The IF function can replace these results with more meaningful text or calculations.
2. Create the Employee Performance Dataset
Create a worksheet named Employee_Performance and enter the following records:
| EmployeeID | EmployeeName | Department | Sales | Target | Attendance | Rating |
|---|---|---|---|---|---|---|
| EMP-101 | Rahul Sharma | Sales | 125000 | 100000 | 95% | 4.5 |
| EMP-102 | Priya Singh | Sales | 85000 | 100000 | 92% | 4.0 |
| EMP-103 | Imran Khan | Marketing | 105000 | 90000 | 88% | 3.8 |
| EMP-104 | Neha Das | Sales | 150000 | 120000 | 97% | 4.8 |
| EMP-105 | Amit Kumar | Marketing | 75000 | 90000 | 82% | 3.2 |
| EMP-106 | Farah Ali | Sales | 110000 | 100000 | 91% | 4.2 |
Convert this dataset into an Excel Table using Ctrl + T and name the table EmployeeData.
3. Using the IF Function
The IF function checks a condition and returns one value when the condition is true and another value when it is false.
IF Function Syntax
=IF(logical_test, value_if_true, value_if_false)Example: Check Whether an Employee Achieved the Target
Suppose Sales is stored in column D and Target is stored in column E. Enter the following formula:
=IF(D2>=E2,"Target Achieved","Target Not Achieved")How the Formula Works
D2>=E2checks whether sales are equal to or greater than the target.- If the condition is TRUE, Excel displays Target Achieved.
- If the condition is FALSE, Excel displays Target Not Achieved.
Structured Reference Version
=IF([@Sales]>=[@Target],"Target Achieved","Target Not Achieved")4. IF Function with Numerical Results
The IF function can return numbers or perform calculations instead of returning only text.
Example: Calculate a 5% Sales Incentive
=IF(D2>=E2,D2*5%,0)If the employee achieves the target, Excel calculates 5% of the sales amount. Otherwise, the incentive is zero.
Structured Reference Formula
=IF([@Sales]>=[@Target],[@Sales]*5%,0)Example Results
| Sales | Target | Incentive |
|---|---|---|
| ₹125,000 | ₹100,000 | ₹6,250 |
| ₹85,000 | ₹100,000 | ₹0 |
| ₹150,000 | ₹120,000 | ₹7,500 |
5. IF Function with Text Conditions
Text values used inside formulas must be enclosed within double quotation marks.
Example: Check Department
=IF(C2="Sales","Sales Team","Other Department")Example: Check Order Status
=IF(B2="Completed","Closed","Pending")Important Note
Excel text comparisons are generally not case-sensitive. Therefore, "Sales", "SALES" and "sales" are usually treated as the same text in an IF comparison.
6. IF Function with Blank Cells
The IF function can check whether a cell is blank before performing a calculation.
Display a Message When Sales Is Missing
=IF(D2="","Sales Not Entered","Sales Available")Prevent a Calculation When Input Is Missing
=IF(OR(D2="",E2=""),"",D2-E2)This formula returns a blank result when Sales or Target has not been entered. Otherwise, it calculates the difference.
7. Nested IF Functions
A nested IF places one IF function inside another IF function. It is used when more than two possible results are required.
Example: Employee Performance Classification
Classify employees based on their target achievement:
- Sales at least 120% of target: Outstanding
- Sales at least 100% of target: Achieved
- Sales at least 80% of target: Near Target
- Sales below 80% of target: Needs Improvement
=IF(D2>=E2*120%,"Outstanding",
IF(D2>=E2,"Achieved",
IF(D2>=E2*80%,"Near Target",
"Needs Improvement")))Structured Reference Version
=IF([@Sales]>=[@Target]*120%,"Outstanding",
IF([@Sales]>=[@Target],"Achieved",
IF([@Sales]>=[@Target]*80%,"Near Target",
"Needs Improvement")))Why the Order of Conditions Matters
Excel checks nested IF conditions from left to right. Therefore, the highest threshold should be tested first. If you check whether sales are greater than the target before checking the 120% condition, outstanding employees may be classified only as “Achieved.”
8. Student Grade Example Using Nested IF
Create a worksheet named Student_Results containing Student Name and Marks. Use the following grading criteria:
| Marks | Grade |
|---|---|
| 90 or above | A+ |
| 80–89 | A |
| 70–79 | B |
| 60–69 | C |
| 50–59 | D |
| Below 50 | Fail |
Grade Formula
=IF(B2>=90,"A+",
IF(B2>=80,"A",
IF(B2>=70,"B",
IF(B2>=60,"C",
IF(B2>=50,"D","Fail")))))Validate the Marks Before Grading
=IF(OR(B2<0,B2>100),"Invalid Marks",
IF(B2>=90,"A+",
IF(B2>=80,"A",
IF(B2>=70,"B",
IF(B2>=60,"C",
IF(B2>=50,"D","Fail"))))))The outer IF checks whether the marks are outside the valid range of 0 to 100.
9. Using the IFS Function
The IFS function checks multiple conditions without requiring several nested IF functions. It is usually easier to read and maintain.
IFS Function Syntax
=IFS(condition1,result1,condition2,result2,condition3,result3)Student Grade Using IFS
=IFS(
B2>=90,"A+",
B2>=80,"A",
B2>=70,"B",
B2>=60,"C",
B2>=50,"D",
TRUE,"Fail"
)The final TRUE,"Fail" works as the default result when none of the earlier conditions are met.
Performance Classification Using IFS
=IFS(
D2>=E2*120%,"Outstanding",
D2>=E2,"Achieved",
D2>=E2*80%,"Near Target",
TRUE,"Needs Improvement"
)Nested IF vs IFS
| Nested IF | IFS |
|---|---|
| Available in older Excel versions | Available in modern Excel versions |
| Can become difficult to read | Cleaner for multiple conditions |
| Contains repeated IF functions | Lists condition-result pairs |
| Supports a direct false result | Often uses TRUE as the final default condition |
10. Using the AND Function
The AND function returns TRUE only when every supplied condition is true.
AND Function Syntax
=AND(condition1,condition2,condition3)Example: Incentive Eligibility
An employee qualifies for an incentive when:
- Sales are equal to or greater than the target.
- Attendance is at least 90%.
- Performance rating is at least 4.
=AND(D2>=E2,F2>=90%,G2>=4)This formula returns TRUE only when all three conditions are satisfied.
Combine IF with AND
=IF(AND(D2>=E2,F2>=90%,G2>=4),
"Eligible",
"Not Eligible")Calculate Incentive Using IF and AND
=IF(AND(D2>=E2,F2>=90%,G2>=4),D2*7%,0)Structured Reference Formula
=IF(
AND(
[@Sales]>=[@Target],
[@Attendance]>=90%,
[@Rating]>=4
),
[@Sales]*7%,
0
)11. Using the OR Function
The OR function returns TRUE when at least one supplied condition is true. It returns FALSE only when every condition is false.
OR Function Syntax
=OR(condition1,condition2,condition3)Example: Priority Customer
A customer is considered a priority customer if either:
- The purchase value is at least ₹100,000, or
- The customer category is Premium.
=IF(OR(B2>=100000,C2="Premium"),"Priority","Regular")Example: Student Pass Criteria
Suppose a student passes by achieving either 50 marks in the final examination or passing an approved supplementary test.
=IF(OR(B2>=50,C2="Pass"),"Qualified","Not Qualified")12. Difference Between AND and OR
| Function | Returns TRUE when | Example use |
|---|---|---|
AND |
All conditions are true | Sales target and attendance requirement are both achieved |
OR |
At least one condition is true | Customer has high sales or belongs to the premium category |
AND Example
=IF(AND(B2>=50,C2>=50),"Pass","Fail")The student must score at least 50 in both subjects.
OR Example
=IF(OR(B2>=50,C2>=50),"Pass","Fail")The student passes if the score is at least 50 in either subject.
13. Combining AND and OR
AND and OR can be combined to create more advanced business rules.
Example: Employee Bonus Rule
An employee receives a bonus when:
- Sales are equal to or greater than the target, and
- Either attendance is at least 90% or the rating is at least 4.5.
=IF(
AND(
D2>=E2,
OR(F2>=90%,G2>=4.5)
),
"Bonus Approved",
"Bonus Not Approved"
)Example: Loan Eligibility
A customer qualifies for a loan when income is at least ₹40,000 and either the credit score is at least 750 or the customer has approved collateral.
=IF(
AND(
B2>=40000,
OR(C2>=750,D2="Yes")
),
"Eligible",
"Not Eligible"
)14. Using the NOT Function
The NOT function reverses a logical result. TRUE becomes FALSE and FALSE becomes TRUE.
NOT Function Syntax
=NOT(logical_test)Example
=NOT(A2="Inactive")If A2 contains Active, the comparison A2="Inactive" is FALSE. The NOT function reverses it to TRUE.
Combine IF with NOT
=IF(NOT(A2="Inactive"),"Account Available","Account Blocked")Check Whether a Cell Is Not Blank
=IF(NOT(A2=""),"Data Entered","Data Missing")15. Understanding Common Excel Errors
Excel displays an error when it cannot complete a formula. Error-handling functions allow you to replace technical errors with a blank, zero or meaningful message.
| Error | Meaning | Common cause |
|---|---|---|
#DIV/0! |
Division by zero | The denominator is zero or blank |
#N/A |
Value not available | A lookup value cannot be found |
#VALUE! |
Invalid value type | Text is used where a number is expected |
#REF! |
Invalid reference | A referenced cell or column was deleted |
#NAME? |
Unrecognized name | A function or named range is misspelled |
#NUM! |
Invalid numerical calculation | The calculation produces an invalid number |
#SPILL! |
Dynamic array cannot expand | Cells are blocking the output range |
16. Using the IFERROR Function
IFERROR returns a specified result when a formula produces any Excel error. If no error occurs, Excel returns the original formula result.
IFERROR Syntax
=IFERROR(value,value_if_error)Division Example
The following formula produces #DIV/0! when B2 is zero:
=A2/B2Use IFERROR to display zero instead:
=IFERROR(A2/B2,0)Display a Custom Message
=IFERROR(A2/B2,"Calculation Error")Return a Blank Cell
=IFERROR(A2/B2,"")Lookup Example
=IFERROR(VLOOKUP(A2,ProductData,3,FALSE),"Product Not Found")If the product code cannot be found, Excel displays “Product Not Found” instead of an error.
17. Using the IFNA Function
IFNA handles only the #N/A error. It is especially useful with lookup formulas when a requested item cannot be found.
IFNA Syntax
=IFNA(value,value_if_na)XLOOKUP Example
=IFNA(XLOOKUP(A2,ProductID,ProductName),"Product Not Found")VLOOKUP Example
=IFNA(VLOOKUP(A2,ProductData,3,FALSE),"Product Not Found")IFERROR vs IFNA
| Function | Errors handled | Recommended use |
|---|---|---|
IFERROR |
All common Excel errors | General error handling |
IFNA |
Only #N/A | Missing lookup results |
Use IFNA when a missing lookup result is expected but other errors should remain visible for investigation. Using IFERROR everywhere can hide genuine problems such as deleted references or incorrect data types.
18. Create a Complete Employee Incentive System
Use the Employee Performance dataset to create the following calculated columns:
Target Status
=IF([@Sales]>=[@Target],"Achieved","Not Achieved")Achievement Percentage
=IFERROR([@Sales]/[@Target],0)Performance Category
=IFS(
[@Sales]>=[@Target]*120%,"Outstanding",
[@Sales]>=[@Target],"Achieved",
[@Sales]>=[@Target]*80%,"Near Target",
TRUE,"Needs Improvement"
)Incentive Eligibility
=IF(
AND(
[@Sales]>=[@Target],
[@Attendance]>=90%,
[@Rating]>=4
),
"Eligible",
"Not Eligible"
)Incentive Rate
=IFS(
[@Sales]>=[@Target]*120%,10%,
[@Sales]>=[@Target],7%,
[@Sales]>=[@Target]*90%,3%,
TRUE,0
)Incentive Amount
=IF(
[@[Incentive Eligibility]]="Eligible",
[@Sales]*[@[Incentive Rate]],
0
)Final Performance Message
=IF(
[@[Incentive Eligibility]]="Eligible",
"Congratulations! Incentive Approved",
"Review Performance Requirements"
)19. Formula-Writing Best Practices
- Write the business rule in plain language before creating the formula.
- Test each logical condition separately.
- Place higher thresholds before lower thresholds.
- Use IFS instead of deeply nested IF formulas when appropriate.
- Use AND when every condition must be satisfied.
- Use OR when any one condition can satisfy the rule.
- Use Excel Tables and structured references for readable formulas.
- Do not unnecessarily hard-code rates inside formulas.
- Store frequently changing rates in separate named cells.
- Use IFNA for expected missing lookup values.
- Do not use IFERROR to hide every error without investigation.
- Test formulas using boundary values such as 49, 50, 79, 80 and 90.
20. Common Formula Mistakes
Mistake 1: Text Without Quotation Marks
=IF(A2=Sales,Yes,No)The correct formula is:
=IF(A2="Sales","Yes","No")Mistake 2: Incorrect Condition Order
=IF(B2>=50,"Pass",IF(B2>=90,"A+","Fail"))A score of 95 is classified as Pass because the first condition is already true. The higher threshold must be checked first.
=IF(B2>=90,"A+",IF(B2>=50,"Pass","Fail"))Mistake 3: Using AND Instead of OR
If a customer qualifies by meeting either one of two conditions, OR should be used.
=IF(OR(B2>=100000,C2="Premium"),"Qualified","Not Qualified")Mistake 4: Hiding Important Errors
=IFERROR(ComplexFormula,"")This formula may hide problems that should be corrected. First identify the reason for the error, and then decide whether IFERROR is appropriate.
21. Practical Assignment: Employee Incentive Calculator
Create an automated Employee Incentive Calculator using at least 20 employee records.
Required Columns
- Employee ID
- Employee Name
- Department
- Monthly Sales
- Monthly Target
- Attendance Percentage
- Performance Rating
- Target Status
- Achievement Percentage
- Performance Category
- Incentive Eligibility
- Incentive Rate
- Incentive Amount
Business Rules
- Sales must be equal to or greater than the target.
- Attendance must be at least 90%.
- Performance rating must be at least 4.
- Employees achieving 120% of target receive a 10% incentive.
- Employees achieving 100% of target receive a 7% incentive.
- Employees achieving 90% of target receive a 3% incentive only when management approves it.
- Employees below 90% receive no incentive.
Assignment Tasks
- Convert the dataset into an Excel Table.
- Name the table
EmployeeData. - Use IF to calculate Target Status.
- Use IFERROR to calculate Achievement Percentage safely.
- Use IFS to assign Performance Category.
- Combine IF with AND to check incentive eligibility.
- Use IFS to calculate the incentive rate.
- Calculate the final incentive amount.
- Apply conditional formatting to highlight eligible employees.
- Filter the table to display only Outstanding employees.
22. Practice Exercises
Exercise 1: Pass or Fail
Write an IF formula that displays Pass when marks are at least 50 and Fail otherwise.
=IF(B2>=50,"Pass","Fail")Exercise 2: Attendance Status
Display Eligible when attendance is at least 75%.
=IF(B2>=75%,"Eligible","Not Eligible")Exercise 3: Multiple-Subject Result
A student passes only when marks in all three subjects are at least 40.
=IF(AND(B2>=40,C2>=40,D2>=40),"Pass","Fail")Exercise 4: Discount Eligibility
A customer receives a discount when the purchase value is at least ₹50,000 or the customer category is Premium.
=IF(OR(B2>=50000,C2="Premium"),"Discount Approved","No Discount")Exercise 5: Safe Division
Calculate Sales divided by Target and return zero if an error occurs.
=IFERROR(B2/C2,0)23. Chapter Quiz
- What type of result does a logical test return?
- What are the three arguments of the IF function?
- Why should the highest threshold be checked first in a nested IF formula?
- What is the main advantage of IFS over nested IF?
- When does the AND function return TRUE?
- When does the OR function return TRUE?
- What does the NOT function do?
- What is the difference between IFERROR and IFNA?
- Which function should be used when a missing lookup result is expected?
- Why should IFERROR not be used to hide every formula error?
24. Frequently Asked Questions
Can IF return a number instead of text?
Yes. IF can return text, numbers, dates, formulas or calculations. For example, =IF(B2>=100000,B2*5%,0) returns an incentive amount.
How many conditions can be used inside an IF formula?
Multiple conditions can be handled by nesting IF functions or by using IFS. However, very long formulas should be simplified using lookup tables or other suitable functions whenever possible.
Should I use nested IF or IFS?
Use IFS when working in a modern Excel version and checking several condition-result pairs. Use nested IF when compatibility with older versions is necessary or when the logic requires a special structure.
Can AND and OR be used together?
Yes. AND and OR can be nested inside each other to represent more complex business rules. Carefully use parentheses to ensure that Excel evaluates the intended conditions.
Does IFERROR correct the original error?
No. IFERROR only replaces the displayed error with another result. It does not correct the underlying problem in the formula or data.
Conclusion
Logical and error-handling functions allow Excel to make decisions automatically. The IF function handles two possible outcomes, while nested IF and IFS manage multiple classifications. AND requires every condition to be true, OR requires at least one condition to be true and NOT reverses a logical result.
IFERROR and IFNA help create user-friendly reports, but they should be used carefully so that genuine formula problems remain visible. After completing the exercises and Employee Incentive Calculator, you should be able to convert real business rules into reliable Excel formulas.
In the next chapter, we will study advanced lookup and reference functions, including VLOOKUP, XLOOKUP, INDEX, MATCH, XMATCH, exact matching, approximate matching and multiple-criteria lookups.