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:

• 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

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

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

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.

✨ Example 4: Grading Students’ Scores

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

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:

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:

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:

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:

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:

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:

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:

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:

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.