This is a second screencast demonstrating goal seek in excel. The first thing we want
to do is find the gravitational force between earth and Pluto. The equation that we are going
to use is that this force is going to equal the gravitational constant times the mass of one object,
which would be Earth, times the mass of a second object, Pluto, all divided by the distance between them. Let's go to
our spreadsheet and see how we do this. Here we have set up a spread sheet that will calculate
the gravitational force between two objects. But we are going to need data for this. We
are going to need the masses as well as the distance between them. Here is our gravitational
constant. The first mass is going to be the mass of the earth. We are going to do the
same thing but with Pluto here. Finally the distance between them. Now we have our values
let's figure out the force. The force is going to equal the gravitational constants times
the first mass times the second mass all divided by the square of the distance between them.
Here is the gravitational force between Pluto and the earth. Let's go back to our question
and see why we might need that data. The second part of this problem we are trying to find
how close Neptune would need to be to earth to have that same gravitational force. You
can see why we needed to calculate that so we can use it in the second part of our problem.
This is force that we are going to need and let's copy the number so that we remember
it. We are going to want to paste it as a value rather than the equation. Our first
mass is correct. That is the mass of the earth. The second mass now has to be the mass of
Neptune. We are not going to change the distance but notice now that the force has changed.
How do we use goal seek? We go into goal seek and set this cell, the force, equal to the
value of 1.4*10^17. We want to change this distance. We click ok and we have a problem.
It says that the cell must contain a value. Goal seek cannot use names for values. You
have to actually have the value in your equation in order to solve it. Let's redo it using
the values. Let's take these values and we are going to copy them and paste them here
as values. Now we are going to rewrite this calculation. This is a force so it is going
to equal the gravitational constant times the first mass which is earth times the second
mass which is Neptune. We are going to divide that by the distance between them squared.
Notice we get the same value for the force that we had before. What we really want is
a force of 1.4*10^17. Now let's go back to goal seek and we are going to set this cell
equal to the value of 1.4*10^17 by changing this distance here. Now when we click ok we
actually have the correct number of 5.4*10^11 meters. That means that if you want that same
gravitational force that you got between Pluto and earth using Neptune's mass the distance
between them would have to be 5.4*10^11 meters. Just as a check let's see what the distance
between them actually is. This is going to equal the distance of Neptune from the sun
minus the distance of earth from the sun. If you notice this is off by more than two
orders of magnitude. This goal seek example shows you not only how to use it but also
one of the things you have to be careful of when you are using this tool.