Excel IFS Function (5 examples)

Excel IFS Function

The Excel IFS function runs multiple tests and returns a value that corresponds to the first TRUE condition. IFS is more powerful than the IF function because IFS can be used in place of multiple nested IF statements. The formula of IFS is much easier, shorter, and to the point.

In this section:

---

1. Syntax of IFS Function
2. Letter grade calculation by using IFS
3. Return names of states based on names of cities
4. Product rating with IFS
5. Good or Bad with IFS
6. Score comparison with IFS

---

1. Syntax of IFS Function:

=IFS(test1, value1, [test2, value2], ..)

where:

• test1: Logical test1, which is a required argument
• value1: Returned value when test1 is TRUE
• test2, value2: Second test/value, which are optional

2. Letter grade calculation by using IFS:

To calculate grade by using IFS is much easier that by using nested IF. The formula to calculate grade is =IFS(D6>95,”A+”,D6>90,”A”,D6>85,”B+”,D6>80,”B”,D6>75,”C+”,D6>=70,”C”,TRUE,”FAIL”), which returns the respective grades.

3. Return names of state based on names of cities:

If the value in C6 is “Miami”, the IFS function returns “FL” and so on.

4. Product rating with IFS:

If cell D5>= 4.5, the IFS function returns “Excellent“, and so on. The formula (in E6) is =IFS(D6>=4.5, “Excellent”, D6>=4.2, “Best”, D6>=4, “Good”, D6>=3.5, “Fair”, TRUE, “No Comment”), which returns the respective comments.

5. Good or Bad with IFS:

If score>= 80, IFS function returns “Good”; otherwise it returns “Bad”. The formula is =IFS(D5>=80, “Good”, TRUE, “Bad”), which returns the respective comments.

6. Score comparison with IFS:

To compare a value of a cell with another value from another cell, users can use IFS function. In our example, the formula is =IFS(D6>$H$7, “Higher”, D6<$H$7, “Lower”, TRUE, “Same”), which returns the respective values. Look, we have locked the cell references $H$7, which you can do by pressing F4 after typing the formula.

More readings:

1. MS Office post of IFS Function
2. excel IF Function
3. Excel SWITCH Function
4. Excel IFNA Function
5. Excel IFERROR Function