# IFS Function in Excel

Part 1. Introduce:

Definition: The IFS function in Microsoft Excel is a powerful logical function that allows you to test multiple conditions and returns a value based on the first condition that evaluates to TRUE. It is useful when you have multiple conditions to check and want to avoid nested IF statements.

Purpose: The purpose of the IFS function is to simplify complex logical tests by providing a cleaner and more concise way to handle multiple conditions in a single formula.

Syntax & Arguments: The syntax of the IFS function is as follows:

syntax
`IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],...) `
• `logical_test1`: The first condition that you want to test.
• `value_if_true1`: The value to be returned if `logical_test1` evaluates to TRUE.
• `logical_test2`: (Optional) Additional condition(s) to test.
• `value_if_true2`: (Optional) The value to be returned if `logical_test2` evaluates to TRUE.
• `logical_test3`: (Optional) Further condition(s) to test.
• `value_if_true3`: (Optional) The value to be returned if `logical_test3` evaluates to TRUE.
• And so on…

Return value: The IFS function returns the value corresponding to the first TRUE condition. If none of the states are TRUE, it will return. `#N/A` (Excel’s error value for “not available”).

Remarks:

• You must have at least one `logical_test` and one corresponding `value_if_true` argument.
• The function stops evaluating conditions once it finds the first TRUE condition.
• IFS function supports up to 127 pairs of `logical_test` and `value_if_true` arguments.

Part 2. Examples of IFS Function:

Example 1: Calculating Performance Rating

ABCD
1SalesTarget SalesActual SalesPerformance Rating
2Product A10001200`=IFS(C2>B2*1.1, "Excellent", C2>B2, "Good", C2<=B2, "Needs Improvement")`
3Product B800750`=IFS(C3>B3*1.1, "Excellent", C3>B3, "Good", C3<=B3, "Needs Improvement")`
4Product C500600`=IFS(C4>B4*1.1, "Excellent", C4>B4, "Good", C4<=B4, "Needs Improvement")`

Explanation:

• We have a sales table with target and accurate sales data.
• The IFS function checks the performance of each product based on the target and actual sales.
• If actual sales are 10% above the target, the product gets an “Excellent” rating.
• If sales exceed the target, it gets a “Good” rating.
• It needs improvement if actual sales are less than or equal to the target.

Example 2: Categorizing Age Group

ABC
1NameAgeAge Group
2John25`=IFS(B2<18, "Teen", B2<30, "Young Adult", B2<50, "Adult", B2>=50, "Senior")`
3Alice12`=IFS(B3<18, "Teen", B3<30, "Young Adult", B3<50, "Adult", B3>=50, "Senior")`
4Bob35`=IFS(B4<18, "Teen", B4<30, "Young Adult", B4<50, "Adult", B4>=50, "Senior")`

Explanation:

• We have a list of individuals with their ages.
• The IFS function categorizes them into different age groups based on their age.
• “Teen” for ages below 18, “Young Adult” for ages between 18 and 29, “Adult” for ages between 30 and 49, and “Senior” for ages 50 and above.

Example 3: Calculating Bonus

ABCD
1NameSalesTarget SalesBonus
2John1200010000`=IFS(B2>C2, (B2-C2)*0.1, B2<=C2, 0)`
3Alice90008000`=IFS(B3>C3, (B3-C3)*0.1, B3<=C3, 0)`
4Bob80009000`=IFS(B4>C4, (B4-C4)*0.1, B4<=C4, 0)`

Explanation:

• We have a table with sales data and corresponding target sales for each employee.
• The IFS function calculates the bonus for each employee based on their sales performance.
• If the sales are above the target, a 10% bonus is given on the extra sales. Otherwise, no prize is awarded.

Suppose we have a table with students’ names and their scores, and we want to assign grades based on their scores:

ABC
2John85`=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")`
3Alice78`=IFS(B3>=90, "A", B3>=80, "B", B3>=70, "C", B3>=60, "D", B3<60, "F")`
4Bob92`=IFS(B4>=90, "A", B4>=80, "B", B4>=70, "C", B4>=60, "D", B4<60, "F")`

Explanation:

• We use the IFS function to assign grades based on student scores.
• If the score is 90 or above, the grade is “A”, and so on.
• If the score exceeds 60, the grade is “F” (fail).

Example 5: Shipping Fee Calculation

Let’s calculate the shipping fee based on the weight of the package and the destination zone:

ABCD
1WeightZoneRateShipping Fee
22.5Zone A3.5`=IFS(B2="Zone A", C2*B2, B2="Zone B", C3*B2, B2="Zone C", C4*B2)`
33.0Zone B2.9`=IFS(B3="Zone A", C2*B3, B3="Zone B", C3*B3, B3="Zone C", C4*B3)`
41.8Zone C4.2`=IFS(B4="Zone A", C2*B4, B4="Zone B", C3*B4, B4="Zone C", C4*B4)`

Explanation:

• We have a table with package weights, destination zones, and corresponding shipping rates.
• The IFS function calculates the shipping fee based on the zone and weight.
• It looks up the rate for the specified zone and multiplies it by the weight.

Example 6: Performance-Based Incentive

Suppose we want to calculate a performance-based incentive for employees based on their sales and years of service:

ABCDE
1NameSalesService YearsIncentiveYears Bonus
2John120005`=IFS(B2>15000, B2*0.08, B2>10000, B2*0.06, B2>5000, B2*0.04, B2<=5000, B2*0.02)``=IFS(C2>=5, B2*0.02, C2>=3, B2*0.01, C2<3, 0)`
3Alice90003`=IFS(B3>15000, B3*0.08, B3>10000, B3*0.06, B3>5000, B3*0.04, B3<=5000, B3*0.02)``=IFS(C3>=5, B3*0.02, C3>=3, B3*0.01, C3<3, 0)`
4Bob80007`=IFS(B4>15000, B4*0.08, B4>10000, B4*0.06, B4>5000, B4*0.04, B4<=5000, B4*0.02)``=IFS(C4>=5, B4*0.02, C4>=3, B4*0.01, C4<3, 0)`

Explanation:

• We have a table with employee sales data and years of service.
• The IFS function calculates the incentive based on their sales performance. Higher sales result in a higher incentive percentage.
• Another IFS function calculates a bonus based on their years of service. More extended service leads to a higher bonus percentage.

Example 7: Awarding Medals in a Sports Event

Let’s award gold, silver, and bronze medals based on participants’ scores in a sports event:

ABCD
1NameScoreMedalMedal Icon
2John95`=IFS(B2>=90, "Gold", B2>=80, "Silver", B2>=70, "Bronze")``=IFS(B2>=90, "🥇", B2>=80, "🥈", B2>=70, "🥉")`
3Alice85`=IFS(B3>=90, "Gold", B3>=80, "Silver", B3>=70, "Bronze")``=IFS(B3>=90, "🥇", B3>=80, "🥈", B3>=70, "🥉")`
4Bob78`=IFS(B4>=90, "Gold", B4>=80, "Silver", B4>=70, "Bronze")``=IFS(B4>=90, "🥇", B4>=80, "🥈", B4>=70, "🥉")`

Explanation:

• We use the IFS function to award medals based on participants’ scores.
• If the score is 90 or above, the participant gets a gold medal, and so on.
• We also use nested IFS functions to display corresponding medal icons (emojis) for better visualization.

Example 8: Evaluating Project Progress

Let’s evaluate the progress of multiple projects based on their completion percentage:

ABCD
1ProjectStatusProgressEvaluation
2Project AOn track75`=IFS(B2="On track", IF(C2>=80, "Excellent", "Good"), B2="Delayed", IF(C2>=50, "Satisfactory", "Needs Improvement"))`
3Project BDelayed40`=IFS(B3="On track", IF(C3>=80, "Excellent", "Good"), B3="Delayed", IF(C3>=50, "Satisfactory", "Needs Improvement"))`
4Project COn track90`=IFS(B4="On track", IF(C4>=80, "Excellent", "Good"), B4="Delayed", IF(C4>=50, "Satisfactory", "Needs Improvement"))`

Explanation:

• We have a table with project statuses and their corresponding progress percentages.
• The IFS function evaluates the project’s progress and provides an overall evaluation.
• If the project is on track and has a progress percentage of 80 or above, it’s considered “Excellent”, and so on.

Example 9: Categorizing Expenses

Let’s categorize expenses based on their amounts into different budget categories:

ABCD
1ExpenseAmountCategoryCategory Icon
2Office Supply100`=IFS(B2<50, "Low", B2<100, "Moderate", B2>=100, "High")``=IFS(B2<50, "💰", B2<100, "💵", B2>=100, "💲")`
3Meals75`=IFS(B3<50, "Low", B3<100, "Moderate", B3>=100, "High")``=IFS(B3<50, "💰", B3<100, "💵", B3>=100, "💲")`
4Equipment1500`=IFS(B4<50, "Low", B4<100, "Moderate", B4>=100, "High")``=IFS(B4<50, "💰", B4<100, "💵", B4>=100, "💲")`

Explanation:

• We use the IFS function to categorize expenses based on their amounts into low, moderate, and high categories.
• Nested IFS functions are also used to display corresponding category icons (emojis) for visual representation.

Example 10: Determining Credit Score Rating

Suppose we want to determine a credit score rating based on the credit score range:

ABC
1NameScoreCredit Rating
2John780`=IFS(B2>=750, "Excellent", B2>=700, "Good", B2>=650, "Fair", B2<650, "Poor")`
3Alice710`=IFS(B3>=750, "Excellent", B3>=700, "Good", B3>=650, "Fair", B3<650, "Poor")`
4Bob650`=IFS(B4>=750, "Excellent", B4>=700, "Good", B4>=650, "Fair", B4<650, "Poor")`

Explanation:

• We use the IFS function to determine the credit score rating based on different score ranges.
• If the score is 750 or above, it gets an “Excellent” rating, and so on.

Example 11: Tiered Discount Calculation

Let’s calculate discounts based on the purchase amount with different discount tiers:

ABCD
1AmountDiscountDiscount AmountTotal Price
2\$500`=IFS(A2<100, 0, A2<300, 0.05, A2<500, 0.1, A2>=500, 0.15)``=B2*A2``=A2-D2`
3\$150`=IFS(A3<100, 0, A3<300, 0.05, A3<500, 0.1, A3>=500, 0.15)``=B3*A3``=A3-D3`
4\$800`=IFS(A4<100, 0, A4<300, 0.05, A4<500, 0.1, A4>=500, 0.15)``=B4*A4``=A4-D4`

Explanation:

• We have a table with purchase amounts and corresponding discounts based on different tiers.
• The IFS function calculates the discount amount based on the purchase amount.
• The discount varies depending on which tier the purchase amount falls into.

Example 12: Performance-Based Bonus Calculation

Let’s calculate performance-based bonuses for employees based on their monthly sales and the average sales of the team:

ABCD
1NameSalesAverageBonus
2John120009000`=IFS(B2>C2, (B2-C2)*0.1, B2>C2*0.9, (B2-C2*0.9)*0.05, B2<=C2*0.9, 0)`
3Alice90007500`=IFS(B3>C3, (B3-C3)*0.1, B3>C3*0.9, (B3-C3*0.9)*0.05, B3<=C3*0.9, 0)`
4Bob80007000`=IFS(B4>C4, (B4-C4)*0.1, B4>C4*0.9, (B4-C4*0.9)*0.05, B4<=C4*0.9, 0)`

Explanation:

• We have a table with employees’ monthly sales and the average sales of their team.
• The IFS function calculates the performance-based bonus based on their sales performance.
• If an employee’s sales exceed the team’s average sales, they get a 10% bonus on the extra sales.
• If their sales are between the average and 90% of the standard, they get a 5% bonus on the additional sales.
• No bonus is awarded if their sales are less than 90% of the average.

📝 Note:

• We used the IFS function in these examples to handle multiple conditions efficiently.
• The formulas in each cell demonstrate how the IFS function can be nested with other functions to create powerful and readable calculations.

🔔 Reminder: When working with the IFS function or any other complex formulas, it’s a good practice to double-check the logic and test it with different scenarios to ensure accurate results.