WEBVTT
Kind: captions
Language: en

00:00:09.540 --> 00:00:14.820
Say I'm taking a class in school, and I'm
not doing very well so far. Luckily, if I

00:00:14.820 --> 00:00:18.650
get a high enough grade on the final exam,
there's a chance I may be able to pass the

00:00:18.650 --> 00:00:19.980
class.

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

00:00:24.850 --> 00:00:25.850
a 70?

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

00:00:30.970 --> 00:00:35.239
information based on the data you already
have.

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

00:00:41.680 --> 00:00:43.370
and 60.

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

00:00:47.950 --> 00:00:49.460
or function in this cell.

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

00:00:55.350 --> 00:01:00.399
need using the AVERAGE function. To get the
most out of What-If Analysis, you do need

00:01:00.399 --> 00:01:05.500
to be fairly comfortable with formulas and
functions—luckily, this one is pretty simple.

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

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

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

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

00:01:27.810 --> 00:01:29.549
average.

00:01:29.549 --> 00:01:33.900
Unfortunately it's not high enough, but we
knew that was going to happen. Now we can

00:01:33.900 --> 00:01:38.509
move onto the next step: using a feature called
Goal Seek to figure out what we need on the

00:01:38.509 --> 00:01:40.540
final exam.

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

00:01:45.009 --> 00:01:50.020
to find a missing value. It starts with the
result you want, then it calculates whatever

00:01:50.020 --> 00:01:51.759
input is needed to get you there.

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

00:02:01.939 --> 00:02:07.490
go to the Data tab… and click the What-If
Analysis command.

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

00:02:13.560 --> 00:02:19.650
and Data Table. But in this case, we're going
to use Goal Seek.

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

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

00:02:33.250 --> 00:02:41.379
by changing the cell that contains our missing
value. And that's going to be B6.

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

00:02:46.450 --> 00:02:49.879
solution.

00:02:49.879 --> 00:02:56.019
Based on the message here, it looks like we
were successful. So we're going to click OK…

00:02:56.019 --> 00:02:59.470
and now we can see the result we were looking
for in the final exam cell.

00:02:59.470 --> 00:03:05.430
So I guess I need to make a 90 on the exam
in order to pass the class. It's going to

00:03:05.430 --> 00:03:07.930
be tough, but I think I can do it.

00:03:07.930 --> 00:03:12.830
At least now I know thanks to the work we
did with What-If Analysis. It might take some

00:03:12.830 --> 00:03:16.590
practice to get the hang of the feature on
your own, but it's a great way to figure out

00:03:16.590 --> 00:03:18.550
unknown values in your worksheet.