Say I'm taking a class in school, and I'm not doing very well so far. Luckily, if I
get a high enough grade on the final exam, there's a chance I may be able to pass the
class.
The question is: What grade do I need to get on the exam to raise my average to at least
a 70?
We can find out using a feature called What-If Analysis, which allows you to fill in missing
information based on the data you already have.
Right now, all I know are the grades I got on the first four assignments: 58, 70, 72,
and 60.
Before we do our analysis, we need to figure out my current average by adding a formula
or function in this cell.
In this case, each assignment is weighted equally, so we can calculate the number we
need using the AVERAGE function. To get the most out of What-If Analysis, you do need
to be fairly comfortable with formulas and functions—luckily, this one is pretty simple.
We'll just type an equals sign, and then AVERAGE.
Next we need to select all the cells—even the empty cell for the final exam grade. If
we don't, it won't calculate the average for all five assignments.
Looks like we're done, so I'm just going to press Enter… and now we can see my current
average.
Unfortunately it's not high enough, but we knew that was going to happen. Now we can
move onto the next step: using a feature called Goal Seek to figure out what we need on the
final exam.
Goal Seek is a type of What-If Analysis that basically lets you work backwards in order
to find a missing value. It starts with the result you want, then it calculates whatever
input is needed to get you there.
To begin, select the cell that contains your formula or function; in our case, B7. Then
go to the Data tab… and click the What-If Analysis command.
There are actually several different types of What-If Analysis, including Scenario Manager
and Data Table. But in this case, we're going to use Goal Seek.
A dialog box will appear where you can set the parameters for the information you need.
In our example, we want to set cell B7 (that's our selected cell)… to a value of 70…
by changing the cell that contains our missing value. And that's going to be B6.
When you're done, click OK… and Goal Seek will take a minute to see if it can find a
solution.
Based on the message here, it looks like we were successful. So we're going to click OK…
and now we can see the result we were looking for in the final exam cell.
So I guess I need to make a 90 on the exam in order to pass the class. It's going to
be tough, but I think I can do it.
At least now I know thanks to the work we did with What-If Analysis. It might take some
practice to get the hang of the feature on your own, but it's a great way to figure out
unknown values in your worksheet.