How IFS works in Excel? Best IFS examples

How IFS works in Excel? Best IFS examples

Excel is an incredibly powerful tool, and one of its most useful features is the IFS function. If you’ve ever struggled with writing complex nested IF statements, this function is about to make your life a lot easier. In this article, I’ll explain how IFS works in Excel and walk you through some of the best IFS examples to illustrate its capabilities.

What is the IFS Function in Excel?

The IFS function simplifies multiple conditional checks by eliminating the need for deeply nested IF statements. It evaluates multiple conditions in order and returns the corresponding value of the first condition that is TRUE.

The basic syntax of the IFS function is as follows:

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ..., TRUE, value_if_false)

Each logical_test is checked sequentially, and as soon as Excel finds a match, it returns the corresponding value_if_true. If no conditions are met and a fallback case is not provided, Excel returns a #N/A error.

Why Use IFS Instead of Nested IF?

Using IFS offers several advantages over traditional nested IF statements:

  • Simplifies formulas: Nested IF statements can get complex and difficult to manage, while IFS keeps it clean and readable.
  • Reduces errors: Managing multiple IF conditions without losing track can be tricky.
  • Improves efficiency: Makes formulas easier to debug and edit.

Best IFS Examples

1. IFS Function for Grading System

Let’s assume we have a grading system where:

  • Scores 90 and above get an “A”
  • Scores 80-89 get a “B”
  • Scores 70-79 get a “C”
  • Scores 60-69 get a “D”
  • Scores below 60 get an “F”

Here’s how we can implement this:

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")

If A2 contains a score of 85, this formula will return “B”. The TRUE at the end serves as a fallback for values lower than 60.

2. IFS Function for Employee Bonuses

Suppose we have a company where bonuses are based on performance ratings:

Performance Score Bonus Percentage
>=90 20%
>=80 15%
>=70 10%
<70 5%

The formula for assigning bonuses would be:

=IFS(B2>=90, 0.2, B2>=80, 0.15, B2>=70, 0.1, TRUE, 0.05)

If an employee’s score in B2 is 75, they will receive a 10% bonus.

3. IFS Function for Status Labels

Imagine we want to assign status labels based on order quantity:

  • >100 = “Bulk Order”
  • 50-100 = “Regular Order”
  • <50 = “Small Order”
=IFS(A2>100, "Bulk Order", A2>=50, "Regular Order", TRUE, "Small Order")

Handling Errors in IFS

Since IFS does not have a default “else” statement like regular IF, missing a fallback condition can lead to a #N/A error. If needed, using IFERROR can help:

=IFERROR(IFS(A2>100, "Bulk Order", A2>=50, "Regular Order", TRUE, "Small Order"), "Error in input")

Conclusion

Now that you know how IFS works in Excel and have seen some of the best IFS examples, you can simplify your conditional logic and improve readability. Whether you’re grading students, calculating bonuses, or managing order statuses, IFS has got you covered. Try implementing it in your own Excel workflows and experience the difference!

 

Other interesting article:

How IFERROR works in Excel? Best IFERROR examples