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:

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”).


  • 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

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")


  • 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

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")


  • 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

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)


  • 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:

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")


  • 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:

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)


  • 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:

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)


  • 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:

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, "🥉")


  • 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:

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"))


  • 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:

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, "💲")


  • 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:

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")


  • 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:

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


  • 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:

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)


  • 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.