If you haven’t already done so, I recommend you download your own copy of this workbook. And we’re going to jump to the Nested If Challenge worksheet. Nested if-3-1.
In this case
we have a Nested IF Challenge with more than two outcomes, and
we’ve got the same individual we used for another IF() Function. Her name is Joan, and she suggested an opportunity for her family, at the end of each month. Plan a family outing together, invite friends in for a special dinner, or enjoy a family or movie night.
And the criteria for her decisions: she wants to propose they have an option to have a family outing if the total remaining between their Family Fun Budget and their Food Budget is at least $150.
If that amount is less than $150 but at least $50, then they can invite some friends in for a special dinner.
However, if the money remaining in those two funds is less than $50, they can still plan a family game or movie night with simple snacks at home. So, your challenge is to follow this IF() Function Development Strategy. And you don’t have to do these steps in a spreadsheet. You could do them on paper, but step b., the first step after defining what the outcomes are, would be for you to set up a table with the outcomes, the three different outcomes, associated with the conditions necessary, for each of those outcomes to be the TRUE outcome. And then you’re going to go on and and I recommend creating your own flow chart, defining your Excel code, building your IF() Functions, and then inserting them into this table. Here’s a table with a simple month tally for three different months, with the dollars remaining in the two different funds. And you could put your IF() Functions in this column and see what your outcomes are. So this is your chance to do this on your own. Once you’ve completed that, I recommend you pause this for that time period, and work on that, in your own companion workbook, and then come back to the solution.
We see on this worksheet, the conditions and outcomes are defined.
The outcomes: the family outing, or the special dinner with friends, or the family game or movie night with snacks, and then the tests would be that the sum of those two budgets was greater than or equal to $150 for the first outcome,
less than $150 but at least $50 for the second outcome, and less than $50 for the third outcome. Now, if we can click on this button and jump to the completed solution:
here we have:
the flow chart setup testing for the two budgets together being >= (greater than or equal to) $150, gets the TRUE outcome. FALSE, we then test: is that budget greater than or equal to $50? Then we have this special dinner with friends. I think in this flow chart that the end of that string was hidden, because of the diamond not being quite big enough. I’m not fixing it,
but you can see the code over here.
And then, if it’s not greater than or equal to $50, then the family game or movie night with snacks. And over here we have the tests and the TRUE and FALSE conditions, defined by Excel code, and then I’m going to
show this is expanded here, we’ve got the generic IF() Function: the second one nested in the first one. And then it’s built out, to contain all of the necessary code. And if we scroll up to the top, to the table, we can see that it actually starts in row four, instead of three, but otherwise the code is the same as the code in this table.
Thank you very much.