← Back to Excel Course

Excel for Data Analytics • Lesson 5

Logical Functions for Data Analytics

Learn how to use IF, AND and OR in Excel to classify data, evaluate business conditions and turn raw numbers into useful analytical information.

BeginnerExcelData AnalyticsLogical Analysis

Why Logical Functions Matter in Data Analytics

Data analysts often need to make decisions based on conditions. For example, a business may want to identify high-value sales, classify customers, or determine whether a target was achieved.

Logical functions allow Excel to evaluate these conditions automatically.

IF

Make a decision based on a condition.

AND

Check whether multiple conditions are all true.

OR

Check whether at least one condition is true.

DATASET

Sales Performance Dataset

Imagine you are a junior data analyst. Your manager wants to identify which sales transactions performed well.

SalespersonRegionUnitsRevenueTarget
AmitNorth12₹120,000₹100,000
NehaSouth7₹70,000₹100,000
RahulEast15₹150,000₹100,000
PriyaWest9₹90,000₹100,000
KaranNorth20₹200,000₹150,000

STEP 1

IF Function: Make a Decision

The IF function checks a condition and returns one result when the condition is true and another result when it is false.

Basic Structure

=IF(condition, value_if_true, value_if_false)

For example, if Revenue is in D2 and the sales target is ₹100,000:

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

CHECK YOUR UNDERSTANDING

A company wants to label each salesperson as 'Target Achieved' when Revenue is at least ₹100,000. Which function is most appropriate?

FORMULA CHALLENGE

Revenue is in D2. Write a formula that returns 'Target Achieved' when D2 is at least ₹100,000 and 'Below Target' otherwise.

fx

DATA ANALYTICS APPLICATION

Use IF to Classify Data

Classification is an important analytical task. Instead of looking at raw numbers, an analyst can create meaningful categories.

SalespersonRevenueClassification
Amit₹120,000Target Achieved
Neha₹70,000Below Target
Rahul₹150,000Target Achieved
Priya₹90,000Below Target
Karan₹200,000Target Achieved

Analytics idea:

We have converted a numerical measure, Revenue, into a useful analytical category: Target Achieved or Below Target.

CHECK YOUR UNDERSTANDING

Which salesperson is classified as 'Below Target'?

STEP 2

AND Function: Check Multiple Conditions

Sometimes one condition is not enough. An analyst may need to check whether two or more conditions are true at the same time.

Example

Suppose a high-performing salesperson must satisfy both conditions:

  • • Revenue is at least ₹100,000
  • • Units sold are at least 10
=AND(D2>=100000,C2>=10)

AND returns TRUE only when both conditions are true.

CHECK YOUR UNDERSTANDING

A salesperson must have Revenue ≥ ₹100,000 AND Units ≥ 10 to qualify as a high performer. Which function should you use?

FORMULA CHALLENGE

Revenue is in D2 and Units are in C2. Write a formula that checks whether Revenue is at least ₹100,000 AND Units are at least 10.

fx

Think Like an Analyst

Look at the dataset carefully. Rahul has 15 units and ₹150,000 revenue. Amit has 12 units and ₹120,000 revenue.

Rahul

Revenue: ₹150,000

Units: 15

Both conditions are TRUE

Amit

Revenue: ₹120,000

Units: 12

Both conditions are TRUE

STEP 3

OR Function: Check Alternative Conditions

OR is useful when at least one condition needs to be true.

Example Business Rule

A manager wants to flag a salesperson if they have either very high revenue OR very high unit sales.

=OR(D2>=150000,C2>=15)

OR returns TRUE if at least one condition is true.

CHECK YOUR UNDERSTANDING

If the rule is Revenue ≥ ₹150,000 OR Units ≥ 15, which salesperson definitely satisfies the rule?

STEP 4

Combine IF with AND

In real analysis, logical functions are often combined. We can use AND inside IF to create an analytical classification.

Business Rule:

If Revenue is at least ₹100,000 AND Units are at least 10, classify the salesperson as "High Performer".

=IF(AND(D2>=100000,C2>=10),"High Performer","Review")

CHECK YOUR UNDERSTANDING

What does the formula =IF(AND(D2>=100000,C2>=10),'High Performer','Review') do?

STEP 5 • DEBUGGING

Find the Logical Error

An analyst wants to identify high performers only when BOTH conditions are satisfied.

Required:

  • • Revenue ≥ ₹100,000
  • • Units ≥ 10

The analyst writes:

=IF(OR(D2>=100000,C2>=10),"High Performer","Review")

CHECK YOUR UNDERSTANDING

Which function should replace OR when BOTH conditions must be satisfied?

BUSINESS CASE

Sales Performance Analysis

Imagine you are preparing a weekly sales report. Your manager wants three categories:

High Performer

Revenue ≥ ₹100,000 AND Units ≥ 10

Target Achieved

Revenue ≥ ₹100,000

Review

Revenue below ₹100,000

FINAL CHALLENGE

Think Like a Data Analyst

You receive a new sales record:

SalespersonRegionUnitsRevenue
SoniaWest11₹105,000

CHECK YOUR UNDERSTANDING

Using the High Performer rule (Revenue ≥ ₹100,000 AND Units ≥ 10), how should Sonia be classified?

FORMULA CHALLENGE

Write the formula that classifies a salesperson as 'High Performer' when Revenue in D2 is at least ₹100,000 AND Units in C2 are at least 10. Otherwise return 'Review'.

fx
🎯

Lesson 5 Complete

You can now use logical functions to evaluate conditions, classify data and create simple analytical business rules.

IFANDORConditionsClassificationBusiness RulesLogical Analysis
Back to Excel Course