Nested IF Function Example 2

Suzanne here. Welcome to Excel Intuition’s Nested If() Example 2 Video. If you haven’t already done so, I recommend you download your own copy of this companion workbook. We’re gonna jump to the Nested if 2-1 worksheet and talk about the situation that we have in front of us. We want to apply the IF() Function Strategy to a problem that has more than two outcomes, that would not lend itself to a “Range Lookup” solution. And in this case, we just have three different outcomes. We’re going to do step a. to “find the outcomes.” So, in this box, we defined somebody named Joan, who has a decision every Saturday morning. And these are her choices: she wants to either 1) do a regular workout at her gym, or 2) an extra challenging workout at her gym, or 3) go for a leisurely stroll downtown and join some friends for coffee. And these are her criteria: she wants to do an extra challenging workout if she’s already dined out more than once that week,

OR has worked out less than twice that week.

If she has NOT dined out more than once, but she’s worked out less than three times that week, she wants to do a regular workout at the gym.

If she has not dined out more than once, and she has worked out at least three times that week, she wants to reward herself with a leisurely stroll downtown, joining her friends for coffee. So Step b. is to create a table matching specific conditions to outcomes. So here in Step b. we have the conditions. We have the outcomes in in the second column: extra challenging workout, regular workout, or leisurely stroll with friends. And they, the conditions sufficient for these outcomes, are listed here: Dine Out Count > (greater than) 1 OR Work Out Count < (less than) 2, if either of those is TRUE, she wants to do an Extra Challenging Workout. For the second one, a regular workout, if her Dine Out Count is <= (less than or equal to) 1 AND her Workout Count is < (less than) 3, then she’ll do a Regular Workout.

However, if her Dine Out Count is <= (less than or equal to) 1 AND her Workout Count is >= (greater than or equal to) 3, then she’ll do a Leisurely Stroll and meet her friends for coffee. So we go on to the next worksheet, Nested if -2-2 and we see the flowchart for this.

We’ve got the first IF() Function doing Test1. We’re going to test for that first outcome, the Extra Challenging Workout. And we’re going to test for Dine Out Count > (greater than) 1 OR Workout Count < (less than) 2. If either of those is TRUE, then our outcome will be “Extra Challenging Workout.” If NEITHER of those outcomes is TRUE then we go on to a second test, (Test2) and we’ll have a second IF() Function holding that second test, in the False argument of our first IF() Function. So our second test (Test2) just has to be, Is the Workout Count < (less than) 3? in order to tell us that she definitely wants to do a regular workout. If she hasn’t done three workouts that week, we know she wants to do one now. We don’t have to test for Dine Out Count >= (greater than or equal to) 1, because

we don’t have to test for Dine Out Count <= (less than or equal to) 1, because we know it MUST BE <= ( less than or equal to) 1, or we wouldn’t have gotten to this stage, because if it was > (greater than) 1, we would have stopped here, because this was an OR. If it was > (greater than) 1, we would have done Extra Challenging Workout. So, it MUST be <= (less than or equal to) 1, so we don’t have to test for it here, and we don’t have to have an AND Function. We can just test for a Workout Count < (less than) 3. So I’m going to scroll down a little, so you can see the whole flow chart.

If

the Workout Count is < (less than) 3, then she’s going to do a Regular Workout, otherwise, if that’s FALSE, we don’t have to test to know that she will choose to do a Leisurely Stroll, because we’ve eliminated these other two outcomes, this is the only one left. We don’t have to test for it. That’s just going to be the Value-if-false for the second test (Test2). Now I’m going to scroll over this way.

Then, the next stage after building the flow chart and labeling it, is to define the actual Excel code for

the Test1, True1, Test2, True2, and False2. So the first test is an OR Function, and we’ve got the two different logical tests within the OR Function.

And we’re, we’re basing the code on this table that we have set up. This is an example of a simple worksheet she keeps on hand. And it has the date that begins the week, and then a count of her workouts during that week following this Sunday’s date. So we’ve got three different examples in here of Workout Counts and Dine Out Counts for these three different weeks, starting with these different Sunday dates. So the the code that we need for Test1 is this OR Function. The code we need for True1 is the text string that would be, would be our outcome, if the Test1 was TRUE. Test2, we just have to test is Workout Count, in this case in M3.

The row has adjusted because of the formatting changing. But basically, if the value in the Workout Count column is < (less than) 3, then what we want to do, she was going to do a “Regular Workout,” otherwise she’s going to have a “Leisurely Stroll with friends.” So these are the values we need to use in our formula. And then, if we actually look into this table, well we could first of all look down here, we’ve built the IF() Function with the generic terms, and then we’ve plugged in the Excel terms for it

to get the Nested IF() Function.

Now, if we click into this cell we see that we actually are starting in row four, not three, because some formatting has changed in this worksheet since this was written up. But the same formula is used down the table and so you can see for example in this cell we’re using values from row five of the table and so on. So we we have results from these different combinations. And you could play around with this on your own, and change the results, and see how the outcomes would change for her choice for Saturday morning.

Thank you.