IF Statements

Lesson 7: IF Statements

The IF function is one of the most important logical functions
in Microsoft Excel. It allows an analyst to make a decision inside a
spreadsheet based on whether a condition is true or false.

Instead of looking at every row manually, an IF statement can automatically
classify, label, approve, reject, flag, or calculate values based on rules.

1. Learning Objectives

By the end of this lesson, you should be able to:

• Explain what conditional logic means.
• Write a basic IF formula in Excel.
• Use comparison operators inside IF statements.
• Work with text and numeric conditions.
• Use IF statements to classify data.
• Create nested IF statements.
• Combine IF with AND and OR.
• Handle blank cells with IF.
• Use IF with dates and thresholds.
• Use relative and absolute references correctly.
• Identify common IF formula errors.
• Test and audit logical formulas.

2. What Is Conditional Logic?

Conditional logic means making a decision based on a condition.

The basic idea is:

If something is true, do one thing; otherwise, do another thing.

For example:

• If a student scores 50 or more, mark the student as Pass.
• If sales are above the target, mark the salesperson as Achieved.
• If attendance is below 75%, flag the student for attention.
• If a payment is overdue, display Overdue.

This makes Excel much more powerful for data analysis because decisions can
be automated across hundreds or thousands of rows.

3. The IF Function

The basic Excel syntax is:

=IF(logical_test, value_if_true, value_if_false)

For example:

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

If the value in cell B2 is 50 or higher, Excel returns Pass.
Otherwise, it returns Fail.

The three parts of IF

1. Logical test — the condition Excel evaluates.
2. Value if true — what Excel should return when the condition is true.
3. Value if false — what Excel should return when the condition is false.

4. Comparison Operators

IF statements commonly use comparison operators.

5. Basic Pass and Fail Example

Imagine a student score dataset:

In C2, use:

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

Copy the formula down the column to classify all students automatically.

6. IF With Numbers

IF statements are frequently used to compare numbers.

Example:

=IF(B2>=100000,"Target Achieved","Below Target")

This can be useful for sales analysis, revenue analysis, budgeting,
performance measurement, and financial reporting.

7. IF With Text

Text comparisons require quotation marks around the text value.

Example:

=IF(B2="Completed","Ready","Pending")

If B2 contains the word Completed, Excel returns
Ready. Otherwise, it returns Pending.

A common mistake is forgetting the quotation marks:

=IF(B2=Completed,"Ready","Pending")

The correct version is:

=IF(B2="Completed","Ready","Pending")

8. IF With Attendance

Suppose a training center requires students to maintain at least 75%
attendance.

Formula:

=IF(B2>=75%,"Eligible","Attendance Too Low")

This can automatically identify students who meet an attendance requirement.

9. IF With Sales Targets

Consider a sales dataset containing salesperson names, sales amounts, and
targets.

Example:

=IF(B2>=C2,"Target Achieved","Target Not Achieved")

Here Excel compares the actual sales in B2 with the target in C2.

This is useful for performance dashboards and management reports.

10. IF With Blank Cells

IF can also be used to identify missing information.

Example:

=IF(A2="","Missing","Complete")

If A2 is empty, Excel returns Missing. Otherwise, it returns
Complete.

This is particularly useful when checking data quality.

11. Handling Zero Values

A zero is not the same as an empty cell.

For example:

=IF(B2=0,"No Sales","Sales Recorded")

This identifies rows where the numeric value is exactly zero.

Analysts should understand the difference between:

• A blank cell
• A zero
• A text value such as “N/A”
• A missing value

12. Classifying Performance

IF statements can be used to create categories.

For example:

=IF(B2>=80,"Excellent",IF(B2>=60,"Good","Needs Improvement"))

This formula creates three categories:

• 80 and above — Excellent
• 60 to 79 — Good
• Below 60 — Needs Improvement

13. Nested IF Statements

A nested IF is an IF statement placed inside another IF statement.

Example:

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

The formula evaluates several conditions in sequence.

Nested IF formulas can be useful, but they can become difficult to read
when too many conditions are added.

For complicated classification rules, newer functions such as
IFS may provide a cleaner approach in supported Excel versions.

14. IF With AND

The AND function allows you to require multiple conditions to be true.

Example:

=IF(AND(B2>=50,C2>=75),"Qualified","Not Qualified")

In this example, the person must:

• Score at least 50.
• Have attendance of at least 75%.

Both conditions must be true before Excel returns
Qualified.

15. IF With OR

The OR function is useful when at least one of several conditions can be
true.

Example:

=IF(OR(B2="Paid",C2="Approved"),"Process","Hold")

If either the payment is marked Paid or the approval status is Approved,
Excel returns Process.

16. The NOT Function

NOT reverses a logical result.

Example:

=IF(NOT(B2="Completed"),"Follow Up","Done")

If B2 is not Completed, the formula returns Follow Up.

17. IF With Dates

Dates can also be tested using IF.

Example:

=IF(B2<TODAY(),"Overdue","Current")

This can be useful for identifying overdue tasks, unpaid invoices,
deadlines, and expired records.

Excel stores dates as numeric values, which makes date comparisons possible.

18. IF With Multiple Functions

IF can be combined with other Excel functions.

For example:

=IF(AVERAGE(B2:D2)>=50,"Pass","Fail")

This calculates the average of three values and then evaluates the result.

Another example:

=IF(SUM(B2:D2)>100000,"High Value","Normal Value")

Combining functions allows analysts to build more useful business rules.

19. Relative References in IF

Consider:

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

When this formula is copied down one row, Excel normally changes B2 to B3.

This is called a relative reference.

Relative references are useful when applying the same rule to many rows.

20. Absolute References in IF

Sometimes the threshold or reference value must remain fixed.

For example, suppose the pass mark is stored in cell F1.

=IF(B2>=$F$1,"Pass","Fail")

The dollar signs keep F1 fixed when the formula is copied.

This is called an absolute reference.

21. Mixed References

Excel also supports mixed references such as:

$A2

and:

A$2

Mixed references are useful when either the row or the column needs to
remain fixed while the other changes.

22. IF for Data Quality Checks

IF statements are useful for identifying possible data-quality problems.

Example:

=IF(B2="","Missing Data","OK")

Another example:

=IF(B2<0,"Check Value","OK")

If negative values are not expected, this formula flags them for review.

This type of logic can be used before analysis to identify suspicious
records.

23. IFERROR

IFERROR is related to logical error handling, but it is not the same as IF.

Example:

=IFERROR(A2/B2,"Check Data")

If the calculation produces an error, Excel returns
Check Data instead of displaying the normal error.

IF evaluates a condition, while IFERROR handles an error result.

24. Common Excel Errors With IF

Forgetting quotation marks

=IF(B2=Pass,"Yes","No")

Incorrect.

Correct:

=IF(B2="Pass","Yes","No")

Missing brackets

Make sure every opening parenthesis has a corresponding closing parenthesis.

Using the wrong comparison operator

For example, > and >= do not mean the same thing.

Incorrect cell references

Always verify that the formula references the correct column and row.

Confusing blanks with zero

An empty cell and a numeric zero are different values.

Overusing nested IF

Very complicated nested formulas can become difficult to maintain and audit.

25. Testing an IF Formula

Never assume a complex formula works simply because Excel accepts it.

Test the formula with different cases:

• A value clearly above the threshold.
• A value clearly below the threshold.
• A value exactly equal to the threshold.
• A blank value.
• An unexpected value.

For example, if the pass mark is 50, test:

• 49
• 50
• 51

This helps confirm that the boundary condition is correct.

26. IF and Business Classification

Businesses often need to classify customers, transactions, products, or
employees.

For example:

=IF(B2>=100000,"Premium","Standard")

This could classify customers based on their purchase value.

Other examples include:

• High-value vs low-value customers
• Active vs inactive accounts
• Paid vs unpaid invoices
• Profitable vs unprofitable products
• Achieved vs missed sales targets
• High-risk vs low-risk transactions

27. IF and Student Analysis

In education, IF statements can automate student classification.

Examples include:

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

=IF(C2>=75%,"Eligible","Review Attendance")

=IF(AND(B2>=50,C2>=75%),"Qualified","Not Qualified")

This reduces repetitive manual checking.

28. IF and Sales Analysis

A sales analyst can use IF to identify whether targets were achieved.

=IF(B2>=C2,"Achieved","Missed")

The same logic can be extended to calculate commissions, identify
underperforming products, or flag sales representatives for review.

29. IFS and SWITCH

Modern versions of Excel provide additional logical functions such as
IFS and SWITCH.

IFS can be useful when several conditions need to be tested without creating
a long chain of nested IF statements.

For example:

=IFS(B2>=80,"Excellent",B2>=60,"Good",B2>=50,"Pass",B2<50,"Fail")

Availability and behavior can vary by Excel version, so analysts should
understand the Excel environment they are working with.

30. Practical Dataset Example

Result formula:

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

Status formula:

=IF(AND(B2>=50,C2>=75%),"Qualified","Not Qualified")

This example demonstrates how several columns can be combined to create a
decision rule.

31. Practical Exercise

Create an Excel dataset containing:

• Student Name
• Score
• Attendance
• Assignment Score
• Result
• Eligibility

Enter at least 10 students.

Then:

1. Use IF to calculate Pass or Fail.
2. Use IF to check attendance.
3. Use AND to determine overall eligibility.
4. Create a performance category.
5. Identify missing values.
6. Copy all formulas down the dataset.
7. Test the formulas using boundary values.

32. Knowledge Check

1. What does the IF function do?
2. What are the three main parts of an IF formula?
3. What does >= mean?
4. Why are quotation marks required around text criteria?
5. What is a nested IF?
6. What is the purpose of AND?
7. What is the purpose of OR?
8. How can IF identify blank cells?
9. What is the difference between a relative and absolute reference?
10. What is IFERROR used for?

33. Key Terms

• Conditional Logic: Making a decision based on a condition.
• IF: Excel function that returns different results depending on whether a condition is true or false.
• Logical Test: The condition being evaluated.
• Comparison Operator: Symbol used to compare values.
• Nested IF: An IF function placed inside another IF function.
• AND: Function that requires all specified conditions to be true.
• OR: Function that returns TRUE when at least one condition is true.
• NOT: Function that reverses a logical result.
• Absolute Reference: A cell reference that remains fixed when copied.
• Relative Reference: A cell reference that changes when copied.
• IFERROR: Function used to return an alternative result when a formula produces an error.

34. Key Takeaways

• IF statements automate decisions in Excel.
• The basic syntax is =IF(condition,true_result,false_result).
• Comparison operators are essential for logical tests.
• Text criteria normally require quotation marks.
• IF can work with numbers, text, dates, blanks, and other functions.
• AND and OR allow more complex conditions.
• Absolute references are useful when a threshold must remain fixed.
• IF statements are valuable for classification and data-quality checks.
• Complex formulas should always be tested with boundary cases.
• IFS and SWITCH may provide cleaner alternatives for some advanced scenarios.

35. Lesson Summary

The IF function is one of the most useful tools for data analysis in Excel.
It allows analysts to transform business rules into repeatable spreadsheet
logic.

By combining IF with comparison operators, AND, OR, NOT, and other Excel
functions, analysts can classify records, evaluate performance, identify
missing information, check eligibility, flag unusual values, and automate
many repetitive decisions.

Mastering IF statements provides an important foundation for more advanced
Excel analysis.

Next Lesson

In the next lesson, we will learn about
SUMIF and COUNTIF and how these functions can be used to
summarize data based on specific conditions.