Creating Business Logic
Swipe to show menu
The dataset now calculates Revenue, but raw numbers alone do not provide much business insight. In this chapter, classify each sale into performance tiers using logical formulas.
Comparison Operators
Excel uses comparison operators to evaluate conditions.
>: greater than;<: less than;>=: greater than or equal to;<=: less than or equal to;=: equal to;<>: not equal to.
These conditions always return either TRUE or FALSE.
IF Function Structure
=IF(logical_test, value_if_true, value_if_false)
Nested IF Structure
=IF(H2>=7000,"High",IF(H2>=3000,"Medium","Low"))
Excel evaluates conditions from left to right.
- First checks
High; - Then checks
Medium; - Otherwise returns
Low.
Logical Functions
AND: returnsTRUEonly if all conditions are true;OR: returnsTRUEif at least one condition is true.
Go to the Sales_Data sheet.
Add a new column named:
Performance Tier
Place the column in I.
Inside I2, type:
=IF(H2>=7000,"High",IF(H2>=3000,"Medium","Low"))
>=7000: High;>=3000: Medium;- Otherwise: Low.
Press Enter.
Review several rows in the dataset and observe how Excel evaluates the formula from left to right.
The first matching condition stops the calculation.
Replace the previous formula with:
=IF(AND(H2>=7000,G2>5),"High",IF(H2>=3000,"Medium","Low"))
For a row to qualify as High, both conditions must be true:
- Revenue must be greater than or equal to
7000; - Units must be greater than
5.
Compare rows that were previously marked as High.
Notice that some rows now move to Medium because both conditions are no longer satisfied.
AND creates stricter rules because every condition must be true.
OR creates looser rules because only one condition must be true.
1. Why does the order of conditions in a nested IF matter?
2. What does the AND function return?
3. What change occurs when replacing AND with OR in the formula?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
