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: