Transcript(click to view)
Welcome to Excel Intuition’s Information Functions Challenge video.
I recommend you download your own copy of the companion workbook so you can try this challenge before you watch the solution. So this would be a good time to pause the video and go to the link in the YouTube entry that will connect you to a download for this video.
At this point I’m going to click on the Retirement Plan link, and give the overview of this challenge. The challenge is a little worksheet that allows you to enter into the yellow cells, a value for an annual salary, and a value for the percent of salary that you would invest in a retirement plan.
Then, ideally, you’d have a calculation of your annual investment, the company’s annual investment, assuming this percent contribution in the total annual investment, and the total monthly investment.
We’ve got two boxes here that give you guidelines for your formulas. Now, the challenge here, is in using the Information Functions correctly, but it’s also in using a nested IF Function effectively. And you have the opportunity to employ the OR Function and the AND Function. So, we have three possible outcomes in the Your Annual Investment cell: the outcome of either a text string that says “Enter your salary and the percent you wish to invest,” and we’re going to have that outcome if either the Salary Value or the Percent of Salary is a NULL value, if either cell is empty.
Another possible outcome, is the actual calculation for Your Annual Investment, which would be your Salary times the Percent of Salary that you want to invest.
A third outcome, is the text string telling the user “Both Salary and Percent of Salary must be numbers.” And the test for that,
would be whether Salary value or Salary Percent were text instead of numbers. But with the way nested IF functions work, we really only have to test for two of these possible outcomes, because since we only have three possible outcomes, if the test for the first outcome and the second outcome, if those tests are FALSE, then we automatically will apply the third outcome. You could draw a flowchart for this, and I’m not going to do that in this solution, but I highly recommend doing it if you feel at all as if you’d like to see the flow better. Then, the second formula we need, is an IF Function for the cells for Company Annual Contribution, Total Annual Investment, and Monthly investment. And those IF Functions will all have the same test. Basically they’re going to test to see whether Your Annual Investment is a number, and if it is, they’re going to perform the appropriate calculations,
calculating the Company’s Annual investment (contribution), based on this Assumed Percent of your investment, and then the Total Annual Investment combining these two, and then the Monthly Investment dividing the Total Annual Investment by 12.
So let’s start this out, we’ve got our first IF function is going to use an OR
to test
if the Salary value or the Percent value is a NULL value. So that’s if this cell or this cell is empty. And for that we use the Isblank Function.
We say, if A4 is blank OR
B4 is blank. We close up the OR function. If either of those is TRUE, then our outcome is going to be the text string:
“enter your Salary and the Percent you wish to invest,”
otherwise
the FALSE value of that IF function, if neither of those tests in the OR Function are TRUE, the FALSE value of the IF function is going to be a second IF function, nested into the first one. This one is going to test that the Salary is a number, AND the Percent of Salary is a number.
So here we’re going to employ an AND Function.
And we’re testing again,
this time we’re using the Isnumber Information Function. We’re again testing cell A4. Is A4 a number? AND
is B4 a number?
close the AND Function.
If it’s TRUE that both of those are numbers, then we’re free to calculate the Annual Investment: Salary times Percent of Salary: Your Annual Investment. So click in A4, multiply that by B4, otherwise,
the FALSE argument to this IF Function, is the default outcome: we’re going to give the message:
“Both Salary and Percent of Salary values must
be numbers.”
And we’re closing up the IF function, the second IF function. We’re closing up the first one, into which this one is nested, and I believe that, let’s take a look at this bar too, you can’t see the whole width of the screen but you should be able to see the function down here.
I think we have all our parentheses in place, so we see the outcome of our nested IF Function is the message: “Enter your Salary and the Percent you is to invest.”
And that’s what we would like the outcome to be, because we have nothing in either of these cells. Now let’s see what happens if
I enter a value in my Salary, let’s say I enter $60,000.
Now, I still get this message “Enter your salary and the percent you wish to invest,” because my first test just tests if either of these is blank, that’s the message I’m going to get. But now, let’s let’s put something in here, but let’s make it not quite what we want. We’re going to do a text string that says “Five %.”
Now we’ve got the second message: “Both Salary and Percent of Salary values must be numbers.”
This is the result we get if the first test isn’t TRUE, and second test is FALSE. If the AND
that tests that both cells are numbers is TRUE,
we would calculate Salary times Percent but if it’s FALSE, we put this message that we’re we’re ending up with here: the result: “Both Salary and Percent of salary must be numbers,” so now I’m going to change this text “Five %” to a numerical 10 %: 0.10.
And now we get Your Annual Investment of $6,000, which is the result of multiplying Salary by the Percent, and let’s just take another look at that formula. I know it’s not ideal to see it so
wrapped instead of seeing it spread out better —-
I’m going to go on —
and we’ll develop our second IF function here. Now this one is going to test to see that Your Annual Investment is a number,
and if it is a number, we’ll calculate the Company’s Annual Contribution. So
we have Isnumber(C4),
if that’s TRUE, then we’ll have Your Annual Investment times the Company’s Projected contribution of 50% of that, otherwise we have a NULL value in this cell because we aren’t ready to do the calculation.
Total Annual Investment, we have the same test: IF(Isnumber(
Your Annual Investment) then our Total Annual Investment is Your Annual Investment plus the Company’s Annual Investment, otherwise this cell will be NULL.
And our final calculation:
again we’re testing to see if Your Annual Investment is a number, if it is, then the Monthly Investment is going to be the Total Annual Investment divided by 12. Otherwise this cell will be NULL.
And we can test again to see that everything clears out by just
emptying the contents of one of these cells. And here we have:
“Enter the Salary and the Percent you wish to invest.” We’ve got the salary already, let’s go with five %,
.05
and we have
all of our calculations working for us. So if you haven’t tried this on your own yet, I again recommend you download the companion workbook and try it on your own and you can watch it again if you need a little bit more help with nesting those ANDs and ORs into an IF Function, and nesting an IF Function into an IF Function. And I could also recommend you to the nested IF Function Example videos that you’ll find on The Logical Functions page of this website, where this video comes from. Thank you.
I recommend you download your own copy of the companion workbook so you can try this challenge before you watch the solution. So this would be a good time to pause the video and go to the link in the YouTube entry that will connect you to a download for this video.
At this point I’m going to click on the Retirement Plan link, and give the overview of this challenge. The challenge is a little worksheet that allows you to enter into the yellow cells, a value for an annual salary, and a value for the percent of salary that you would invest in a retirement plan.
Then, ideally, you’d have a calculation of your annual investment, the company’s annual investment, assuming this percent contribution in the total annual investment, and the total monthly investment.
We’ve got two boxes here that give you guidelines for your formulas. Now, the challenge here, is in using the Information Functions correctly, but it’s also in using a nested IF Function effectively. And you have the opportunity to employ the OR Function and the AND Function. So, we have three possible outcomes in the Your Annual Investment cell: the outcome of either a text string that says “Enter your salary and the percent you wish to invest,” and we’re going to have that outcome if either the Salary Value or the Percent of Salary is a NULL value, if either cell is empty.
Another possible outcome, is the actual calculation for Your Annual Investment, which would be your Salary times the Percent of Salary that you want to invest.
A third outcome, is the text string telling the user “Both Salary and Percent of Salary must be numbers.” And the test for that,
would be whether Salary value or Salary Percent were text instead of numbers. But with the way nested IF functions work, we really only have to test for two of these possible outcomes, because since we only have three possible outcomes, if the test for the first outcome and the second outcome, if those tests are FALSE, then we automatically will apply the third outcome. You could draw a flowchart for this, and I’m not going to do that in this solution, but I highly recommend doing it if you feel at all as if you’d like to see the flow better. Then, the second formula we need, is an IF Function for the cells for Company Annual Contribution, Total Annual Investment, and Monthly investment. And those IF Functions will all have the same test. Basically they’re going to test to see whether Your Annual Investment is a number, and if it is, they’re going to perform the appropriate calculations,
calculating the Company’s Annual investment (contribution), based on this Assumed Percent of your investment, and then the Total Annual Investment combining these two, and then the Monthly Investment dividing the Total Annual Investment by 12.
So let’s start this out, we’ve got our first IF function is going to use an OR
to test
if the Salary value or the Percent value is a NULL value. So that’s if this cell or this cell is empty. And for that we use the Isblank Function.
We say, if A4 is blank OR
B4 is blank. We close up the OR function. If either of those is TRUE, then our outcome is going to be the text string:
“enter your Salary and the Percent you wish to invest,”
otherwise
the FALSE value of that IF function, if neither of those tests in the OR Function are TRUE, the FALSE value of the IF function is going to be a second IF function, nested into the first one. This one is going to test that the Salary is a number, AND the Percent of Salary is a number.
So here we’re going to employ an AND Function.
And we’re testing again,
this time we’re using the Isnumber Information Function. We’re again testing cell A4. Is A4 a number? AND
is B4 a number?
close the AND Function.
If it’s TRUE that both of those are numbers, then we’re free to calculate the Annual Investment: Salary times Percent of Salary: Your Annual Investment. So click in A4, multiply that by B4, otherwise,
the FALSE argument to this IF Function, is the default outcome: we’re going to give the message:
“Both Salary and Percent of Salary values must
be numbers.”
And we’re closing up the IF function, the second IF function. We’re closing up the first one, into which this one is nested, and I believe that, let’s take a look at this bar too, you can’t see the whole width of the screen but you should be able to see the function down here.
I think we have all our parentheses in place, so we see the outcome of our nested IF Function is the message: “Enter your Salary and the Percent you is to invest.”
And that’s what we would like the outcome to be, because we have nothing in either of these cells. Now let’s see what happens if
I enter a value in my Salary, let’s say I enter $60,000.
Now, I still get this message “Enter your salary and the percent you wish to invest,” because my first test just tests if either of these is blank, that’s the message I’m going to get. But now, let’s let’s put something in here, but let’s make it not quite what we want. We’re going to do a text string that says “Five %.”
Now we’ve got the second message: “Both Salary and Percent of Salary values must be numbers.”
This is the result we get if the first test isn’t TRUE, and second test is FALSE. If the AND
that tests that both cells are numbers is TRUE,
we would calculate Salary times Percent but if it’s FALSE, we put this message that we’re we’re ending up with here: the result: “Both Salary and Percent of salary must be numbers,” so now I’m going to change this text “Five %” to a numerical 10 %: 0.10.
And now we get Your Annual Investment of $6,000, which is the result of multiplying Salary by the Percent, and let’s just take another look at that formula. I know it’s not ideal to see it so
wrapped instead of seeing it spread out better —-
I’m going to go on —
and we’ll develop our second IF function here. Now this one is going to test to see that Your Annual Investment is a number,
and if it is a number, we’ll calculate the Company’s Annual Contribution. So
we have Isnumber(C4),
if that’s TRUE, then we’ll have Your Annual Investment times the Company’s Projected contribution of 50% of that, otherwise we have a NULL value in this cell because we aren’t ready to do the calculation.
Total Annual Investment, we have the same test: IF(Isnumber(
Your Annual Investment) then our Total Annual Investment is Your Annual Investment plus the Company’s Annual Investment, otherwise this cell will be NULL.
And our final calculation:
again we’re testing to see if Your Annual Investment is a number, if it is, then the Monthly Investment is going to be the Total Annual Investment divided by 12. Otherwise this cell will be NULL.
And we can test again to see that everything clears out by just
emptying the contents of one of these cells. And here we have:
“Enter the Salary and the Percent you wish to invest.” We’ve got the salary already, let’s go with five %,
.05
and we have
all of our calculations working for us. So if you haven’t tried this on your own yet, I again recommend you download the companion workbook and try it on your own and you can watch it again if you need a little bit more help with nesting those ANDs and ORs into an IF Function, and nesting an IF Function into an IF Function. And I could also recommend you to the nested IF Function Example videos that you’ll find on The Logical Functions page of this website, where this video comes from. Thank you.