WEBVTT
Kind: captions
Language: en

00:00:11.440 --> 00:00:15.679
Say I'm taking a class in school, and I'm
not doing very well so far.

00:00:15.679 --> 00:00:20.070
Luckily, if I get a high enough grade on the
final exam, there's a chance I may be able

00:00:20.070 --> 00:00:21.970
to pass the class.

00:00:21.970 --> 00:00:26.750
The question is: What grade do I need to get
on the exam to raise my average to at least

00:00:26.750 --> 00:00:28.210
a 70?

00:00:28.210 --> 00:00:32.870
We can find out using a feature called What-If
Analysis, which allows you to fill in missing

00:00:32.870 --> 00:00:37.200
information based on the data you already
have.

00:00:37.200 --> 00:00:43.860
Right now, all I know are the grades I got
on the first four assignments: 58, 70, 72,

00:00:43.860 --> 00:00:45.300
and 60.

00:00:45.300 --> 00:00:49.870
Before we do our analysis, we need to figure
out my current average by adding a formula

00:00:49.870 --> 00:00:53.170
or function in this cell.

00:00:53.170 --> 00:00:57.319
In this case, each assignment is weighted
equally, so we can calculate the number we

00:00:57.319 --> 00:00:59.969
need using the AVERAGE function.

00:00:59.969 --> 00:01:03.949
To get the most out of What-If Analysis, you
do need to be fairly comfortable with formulas

00:01:03.949 --> 00:01:07.530
and functions—luckily, this one is pretty
simple.

00:01:07.530 --> 00:01:13.910
We'll just type an equals sign, and then AVERAGE.

00:01:13.910 --> 00:01:18.960
Next we need to select all the cells—even
the empty cell for the final exam grade.

00:01:18.960 --> 00:01:25.030
If we don't, it won't calculate the average
for all five assignments.

00:01:25.030 --> 00:01:29.760
Looks like we're done, so I'm just going to
press Enter… and now we can see my current

00:01:29.760 --> 00:01:31.620
average.

00:01:31.620 --> 00:01:35.420
Unfortunately it's not high enough, but we
knew that was going to happen.

00:01:35.420 --> 00:01:39.940
Now we can move onto the next step: using
a feature called Goal Seek to figure out what

00:01:39.940 --> 00:01:42.610
we need on the final exam.

00:01:42.610 --> 00:01:46.960
Goal Seek is a type of What-If Analysis that
basically lets you work backwards in order

00:01:46.960 --> 00:01:49.120
to find a missing value.

00:01:49.120 --> 00:01:53.110
It starts with the result you want, then it
calculates whatever input is needed to get

00:01:53.110 --> 00:01:56.540
you there.

00:01:56.540 --> 00:02:01.540
To begin, select the cell that contains your
formula or function; in our case, B7.

00:02:01.540 --> 00:02:11.390
Then go to the Data tab… and click the What-If
Analysis command.

00:02:11.390 --> 00:02:15.730
There are actually several different types
of What-If Analysis, including Scenario Manager

00:02:15.730 --> 00:02:17.470
and Data Table.

00:02:17.470 --> 00:02:20.580
But in this case, we're going to use Goal
Seek.

00:02:20.580 --> 00:02:27.150
A dialog box will appear where you can set
the parameters for the information you need.

00:02:27.150 --> 00:02:35.470
In our example, we want to set cell B7 (that's
our selected cell)… to a value of 70…

00:02:35.470 --> 00:02:39.220
by changing the cell that contains our missing
value.

00:02:39.220 --> 00:02:43.690
And that's going to be B6.

00:02:43.690 --> 00:02:49.000
When you're done, click OK… and Goal Seek
will take a minute to see if it can find a

00:02:49.000 --> 00:02:52.040
solution.

00:02:52.040 --> 00:02:55.410
Based on the message here, it looks like we
were successful.

00:02:55.410 --> 00:03:00.250
So we're going to click OK… and now we can
see the result we were looking for in the

00:03:00.250 --> 00:03:02.060
final exam cell.

00:03:02.060 --> 00:03:07.160
So I guess I need to make a 90 on the exam
in order to pass the class.

00:03:07.160 --> 00:03:09.560
It's going to be tough, but I think I can
do it.

00:03:09.560 --> 00:03:14.209
At least now I know thanks to the work we
did with What-If Analysis.

00:03:14.209 --> 00:03:17.790
It might take some practice to get the hang
of the feature on your own, but it's a great

00:03:17.790 --> 00:03:20.269
way to figure out unknown values in your worksheet.