Perfect channel to learn Data Analytics
Learn SQL, Python, Alteryx, Tableau, Power BI and many more
For Promotions: @coderfun @love_data
Post #3033
3.17K
🚀 Data Analyst Roadmap — Part 4
📊 Excel — Level 3: Conditional Functions
Now that you understand basic Excel formulas, the next step is learning how to make Excel make decisions based on conditions.
This is a very important skill for Data Analysts because real-world questions are rarely just:
Instead, you'll get questions like:
To answer these questions, you need conditional functions.
1️⃣ IF()
IF() is one of the most important Excel functions.
It allows Excel to make a decision.
Syntax
Think of it as:
Example
Suppose sales are in B2.
You want to classify employees:
Sales ≥ 50,000 → High
Sales < 50,000 → Low
If B2 is:
75,000
Result: High
If B2 is:
35,000
Result: Low
2️⃣ IF() in Real-World Data Analysis
Suppose you have:
Employee | Sales
John | 75,000
Sarah | 45,000
Mike | 90,000
David | 30,000
You can create a performance column:
Result:
Employee | Sales | Status
John | 75,000 | Target Achieved
Sarah | 45,000 | Target Not Achieved
Mike | 90,000 | Target Achieved
David | 30,000 | Target Not Achieved
This is called data categorization.
3️⃣ Multiple Conditions with Nested IF()
Sometimes you need more than two categories.
For example:
≥ 80,000 → Excellent
≥ 60,000 → Good
≥ 40,000 → Average
< 40,000 → Poor
You can use:
Excel checks the conditions from left to right.
Important: The order matters. You should generally check the highest threshold first.
4️⃣ IFS()
IFS() is a cleaner alternative when you have multiple conditions.
The first condition that evaluates to TRUE determines the result.
IF vs IFS
Use:
5️⃣ AND()
AND() checks whether all conditions are true.
Example
You want to identify employees who:
Belong to IT AND earn more than ₹80,000
Both conditions must be true.
6️⃣ Combining IF() + AND()
This is more useful in real analysis.
Meaning:
7️⃣ OR()
OR() checks whether at least one condition is true.
Example:
You want to identify employees who belong to either:
IT OR Finance
If either condition is true, the result is TRUE.
8️⃣ Combining IF() + OR()
📊 Excel — Level 3: Conditional Functions
Now that you understand basic Excel formulas, the next step is learning how to make Excel make decisions based on conditions.
This is a very important skill for Data Analysts because real-world questions are rarely just:
"What is the total?"
Instead, you'll get questions like:
"What are the total sales for the IT department?"
"How many employees earn more than ₹80,000?"
"What is the average sales for the North region?"
"Which employees achieved their target?"
To answer these questions, you need conditional functions.
1️⃣ IF()
IF() is one of the most important Excel functions.
It allows Excel to make a decision.
Syntax
=IF(condition, value_if_true, value_if_false)Think of it as:
If something is true → do this; otherwise → do that.
Example
Suppose sales are in B2.
You want to classify employees:
Sales ≥ 50,000 → High
Sales < 50,000 → Low
=IF(B2>=50000,"High","Low")If B2 is:
75,000
Result: High
If B2 is:
35,000
Result: Low
2️⃣ IF() in Real-World Data Analysis
Suppose you have:
Employee | Sales
John | 75,000
Sarah | 45,000
Mike | 90,000
David | 30,000
You can create a performance column:
=IF(B2>=50000,"Target Achieved","Target Not Achieved")Result:
Employee | Sales | Status
John | 75,000 | Target Achieved
Sarah | 45,000 | Target Not Achieved
Mike | 90,000 | Target Achieved
David | 30,000 | Target Not Achieved
This is called data categorization.
3️⃣ Multiple Conditions with Nested IF()
Sometimes you need more than two categories.
For example:
≥ 80,000 → Excellent
≥ 60,000 → Good
≥ 40,000 → Average
< 40,000 → Poor
You can use:
=IF(B2>=80000,"Excellent",IF(B2>=60000,"Good",IF(B2>=40000,"Average","Poor")))Excel checks the conditions from left to right.
Important: The order matters. You should generally check the highest threshold first.
4️⃣ IFS()
IFS() is a cleaner alternative when you have multiple conditions.
=IFS(
B2>=80000,"Excellent",
B2>=60000,"Good",
B2>=40000,"Average",
TRUE,"Poor"
)
The first condition that evaluates to TRUE determines the result.
IF vs IFS
Use:
IF() → simple decisionsIFS() → multiple conditions 5️⃣ AND()
AND() checks whether all conditions are true.
Example
You want to identify employees who:
Belong to IT AND earn more than ₹80,000
=AND(B2="IT",C2>80000)Both conditions must be true.
6️⃣ Combining IF() + AND()
This is more useful in real analysis.
=IF(AND(B2="IT",C2>80000),"Eligible","Not Eligible")Meaning:
If the employee is from IT AND salary is greater than ₹80,000, return "Eligible".
Otherwise: "Not Eligible"
7️⃣ OR()
OR() checks whether at least one condition is true.
Example:
You want to identify employees who belong to either:
IT OR Finance
=OR(B2="IT",B2="Finance")If either condition is true, the result is TRUE.
8️⃣ Combining IF() + OR()
=IF(
OR(B2="IT",B2="Finance"),
"Technical Department",
"Other"
)
