CS 100 (Learn) — CS 100 (Web) — Module 06
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video, we are going to explore how to use the What-If analysis tool within Excel.
Let's say we are keeping track of our grades in a course (not this course... one of your other courses).
There might be a participation grade (which is 5%), assignments (worth 25%), a midterm (worth 30%), and a final exam (worth 40%).
Let's [add a field name for] weight and grade.
Let's say your participation grade is excellent: you have a 90% in your participation. On your assignments, you did pretty good (75%). On your midterm, you were a little weak you got 60% and you are going into your final exam.
You want to know what you need to get on your final exam to pull off an 'A' in this course.
Let's calculate what your final grade would be. To do your final grade, we are going to use a fancy function you may not have seen before (but why not learn a new function?) called SUMPRODUCT. It takes a range and then it multiplies each one [in the range] by its corresponding entry in [another] range.
That will calculate our final grade.
41? That is not great... [but] do not panic: you do not have a final exam grade in there yet.
If you pass the final exam [with a 50] you will get a sixty (61.25). What if you get an 80 on your final exam? You will end up with a 73 (73.25). You can keep doing this and try to drill down and try to figure out exactly what you need on your final exam to get that 'A' in the course.
It out somewhere around ninety-eight is what you need in this particular scenario.
Is there a more advanced way of doing this? Absolutely!
First, let's make it a little easier... let's say you got a 75 on the midterm: that is going to make your life a little bit easier (to get that 'A').
On the "data tab", I [click on] "What-If analysis", and the only [tool] we are going to look at today is "goal seek": there are some other advanced what-if analysis you can be using (there are dozens of features in Excel that we have not even touched in this course).
Let's take a look at "goal seek".
I want my final grade cell (D8) to be an 80, and I want Excel [to change the final exam] cell (D6).
Excel has calculated if we get exactly 85.625 on our final grade exam, our final grade will be an 80.
[Perhaps] I can not pull that off: let's do another what-if analysis and [consider] if I want [the final grade] to be a 70: what do I need to get a 70 in this course? I only need a 60.625.
What if you are really being not very ambitious: What do I need to pass this course? I want my final grade to be 50, modify my final exam... Oh, I only need 10% on my final exam to pass this course: I am flying! let's go to [a] movie!
[The example we used for the] what-if analysis goal seek tool [was] very straightforward. It can [also] be used for more advanced functions.
For example, I will bring up a new worksheet: I have a number (and right now it is 10).
I have a more advanced formula, [but first] (I am going to name my cell "num" to make my life easier).
[My formula will be]
=num+3*num+2*num^2-num^3
It is a bit more of a complicated formula. For the number 10, it is -760.
I [use] what-if analysis: I want to know what value of number ("num") will make [the value of this formula] zero.
I want to set [the] cell [with the formula] to be zero by changing [the] cell [with the number].
Notice we [the result] is very close to zero. If I illustrate it this way [change the formatting] you really see that it [was really] close to zero, so the "root" of that function [in the formula] is somewhere around 3.236.
That is an example of using a more advanced formula with the what-if scenario [tool].