 Excel 2019/365: Making Decisions with the IFS Function

Updated 2019-09-15

Objectives:

·         Use the IFS function to make a decision about the value to place in a cell.

The IFS Function

Download the samples workbook. Go to the IFS worksheet.

IFS function overview

The IF function allows you to choose one of many values to put into a cell. For example, a teacher might want to create a spreadsheet that puts the letter "A", "B", "C", "D", or "F" in a cell, depending on the student's average for the class. This rule has 10 parts (it will always have twice as many parts as there are choices for the values to go in the cell):

1.      The condition for an "A".

2.      "A"

3.      The condition for a "B".

4.      "B"

5.      The condition for a "C".

6.      "C"

7.      The condition for a "D".

8.      "D"

9.      TRUE

10.   "F"

IFS function arguments

An IFS function must always be supplied with pairs of arguments. Each pair consists of:

1.      A logical test

2.      A value to appear in the cell if the logical test is TRUE

The logical tests are evaluated in order. As soon as one logical test is TRUE, the argument that follows it is placed in the cell.

Consider the following data (from the IFS worksheet): If our grading scale is:

A:           90 to 100

B:           80 to less than 90

C:           70 to less than 80

D:           60 to less than 70

F:            Less than 60

Then this is what the IFS function would look like for George Washington (row 2). I have added extra spaces to emphasize the argument pairs:

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

And this is how it is evaluated:

1.      If C2 >= 90 is TRUE, put "A" in the cell.
=IFS(C2>=90,"A", C2>=80, "B", C2>=70, "C", C2>=60, "D", TRUE, "F")

2.      If C2 >= 90 is FALSE, go to the next logical test (C2>=80). If C2>=80 is TRUE, put "B" in the cell
=IFS(C2>=90,"A", C2>=80, "B", C2>=70, "C", C2>=60, "D", TRUE, "F")

3.      If C2 >= 80 is FALSE, go to the next logical test (C2>=70). If C2>=70 is TRUE, put "C" in the cell
=IFS(C2>=90,"A",C2>=80, "B", C2>=70, "C", C2>=60, "D", TRUE, "F")

4.      If C2 >= 70 is FALSE, go to the next logical test (C2>=60). If C2>=60 is TRUE, put "D" in the cell
=IFS(C2>=90,"A",C2>=80, "B", C2>=70, "C", C2>=60, "D", TRUE, "F")

5.      If C2 >= 60 is FALSE, go to the next logical test: TRUE. If TRUE is TRUE (and it always is!), put "F" in the cell.
=IFS(C2>=90,"A",C2>=80, "B", C2>=70, "C", C2>=60, "D", TRUE, "F")

There are several things to note:

1.      The logical tests are always evaluated in order from left to right.

2.      As soon as a logical test evaluates to TRUE, no more logical tests will be evaluated.

3.      To make sure that the last option is always chosen if all logical tests are FALSE, the "logical test" for the last argument is always TRUE.

4.      The order of the tests makes a difference. If the first two logical tests were reversed, like this:
=IFS(C2>=80,"B", C2>=90, "A", C2>=70, "C", C2>=60, "D", TRUE, "F")

Then any student with an average of 80 or higher would be assigned a "B". NO STUDENT WOULD BE ASSIGNED AN "A"! So you must always start at one end of the range of possibilities and work your way towards the other end.

It would also be possible to have written the IFS like this, starting at the low end and working towards the high end.

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

After entering the IFS function in column D, the first few rows of the spreadsheet look like this: 