From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365

Use IF() and IFS() in nested functions

From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365

Start my 1-month free trial

Use IF() and IFS() in nested functions

“

- In our previous video, we were nesting AND, OR, and NOT statements within an IF statement. I want to take a look at using just a simple IF statement, which requires that we only return one set of criteria and also the IFS statement, in which we can set two or more sets of criteria. And depending upon which set of criteria the result meets, we can return different results or text. Let's take a look at our IF, IFS workbook. And our first question relates to whether or not our employees met their goal of $500,000 in sales. And I have the statements over in column I for the three questions that we're trying to answer. So, the first one asks us, beginning in D6 for each employee, enter yes if the goal was met and no if it was not met. Well, we can accomplish this using a simple IF statement. Remember that your IF statements are simply true and false. It either meets the criteria, and thus a true is returned, or it doesn't meet the criteria, and false is returned. So let's begin in D6, and type =IF. Open our parenthesis, and our first logical test is does the sales amount in C6 exceed or is it equal to $500,000, which is our sales goal? I'll put a comma in, and the value that I want returned if it's true will be the word yes. If it's false, I want excel to return no. Now, if I don't type anything in here, it will simply return true or false. Okay, let's hit enter. And our first employee did not meet their sales goal. Let's double click on the auto-fill handle to copy that formula down. And it looks like the first employee who met their sales goal is Chiquita Walsh. Let's take a look at our next question. This pertains also to the $500,000 sales goal. This is going to be a little different though. If they met the goal, we want to return Good. If they exceeded that goal, we want to return Great. And if the employee did not meet the goal, the word Poor will be returned. So we are going to use three different sets of criteria. And we're going to tell excel what to enter based on which set of criteria is met. And we'll use an IFS statement since we had more than one set of criteria. Equals IFS, and the first set will be if the sales goal equals $500,000 comma return Good comma. Our second set of criteria is if C6 is greater than $500,000 then we want it to return the word Great and a comma. And so our third set of criteria is IF it is less than $500,000, return Poor. C6 < $500,000, Poor. And you can have as many statements in here as you want, or rather as many sets of criteria as you want. And again, based on the result in C6, either Good, Great, or Poor will be returned. I'll go ahead and hit enter. And of course our first employee did not meet that goal. Let's go ahead and copy the formula down, and we can see that Chiquita exceeded that goal. So, hers is Great. Now, I'm going to leave the question that we want to answer beginning in G6 for you to do, so that you can get a little practice, and you will want to use an IF statement, because we are looking at three sets of criteria very similar to what we did in column F.

Contents