This video is an introduction to using solver in Excel 2010. Solver is a utility similar
to goal seek. It has the capability to solve the same kinds of problems that goal seek
can as well as more complex ones. Solver is located on the data tab of the ribbon in the
analysis panel. As you can see here it is not listed. Solver is automatically installed
with Excel but it is not automatically activated. To activate solver you go to "file", "options",
"add-ins", "manage excel add-ins", "go", and then you select solver add-in. As you can
see solver is now in the analysis panel of data tab. I have an example problem set up
to show you how to use solver. This example problem has 3 questions. The first is "Using
the provided spreadsheet find out how much you would weigh on Jupiter." This first question
actually does not use solver but it will introduce you to how I set up this worksheet. Here we need to
first enter a weight. I am going to enter 155 lbs. It automatically updated the weight
in kilograms and the gravitational force generated by earth. Now we need to include the planet
mass and radius of Jupiter. The 3rd spreadsheet on this workbook has the data from the planets.
The data from Jupiter is in this row. I am going highlight this and paste it over here
so I can read it more easily. This first value here is the mass and the second one is the
radius. As you can see the spreadsheet has updated again. Now it has included the converted
weights in kilograms and pounds as well as the planet density. It also showed here how
much more you weight on Jupiter than you do on earth. So on Jupiter someone who weighed
155 pounds would weigh 392. The second question is what is the radius of the planet that would
make you weigh the same as you do on earth if it had the same density as Pluto. So what
we need is we need the density of Pluto to begin with. So we go back here. We have to
change these values to make the density equal to Pluto. So we find out what that is. We
go to extra data. Pluto is right here and the density is already listed. So instead
we can just copy this and paste it for reference over here. So here is the density of Pluto.
We need to find out what the radius would be of a planet that would make this value
the same as this value. So what we do now that we have the density of Pluto over here
is we highlight and copy this number and then we start solver. The solver screen as you
can see is rather large so it is covering up the spreadsheet. While I'm showing you
this example I am going to have to continually move it off the screen to show what is happening
on the spreadsheet itself. So first we need to set the objective. This is the value that
we want to change and that would be the value in "F8" which is this mass here. We want this
value to equal this value here. So if we set "F8" to the value of 155 pounds then it's
going cause changes until it can get this value to match that one, but we need to tell
it what to change and that's here, by changing the variable cells, "F10" and "F11". Because
although we're just looking for the radius we want to allow the mass and the radius to
change because we need the density to update. The density is a constraint so that's in subjects
to the constraints box here. We select add constraint. We pick the cell reference as
here, equal to and the value that we copied previously which is the density of Pluto.
If we select "OK" it will add the constraint in this box. Now that we have everything set
up here we can select "solve" and solver goes through and solves the solution for us. I
will move this off the screen so you can see what it has come up with. Here you can see
that the weight is now 155 pounds and it did it by changing the mass and the radius. So
in the solver box you can either select to have it keep the solver solution or to restore
the original values. I want to keep the original solution. We would like to see how much these
have changed from what it used to be. If you remember the original values were for Jupiter.
So I am going back here and copy these back. Jupiter originally had a mass of 1.9*10^27
and it has gone down to 4.5*10^25. So here the planet radius has decreased from 71 million
to 17 million. So both the mass and the radius had to decrease. But compared to Pluto the
mass has gone up and the radius has gone up as well. So the radius here is the value that
answers that question. The third question is "How much would you have to weigh on Mercury
to weigh the same as you do on earth." What this questions is asking is what would this
weight have to be on Mercury to make the pounds here equal to that 155? First we need to get
the information for Mercury. The mass of Mercury goes here and the radius goes here. So on
Mercury if you weighed 155 pounds you would now weigh 58.5 pounds. We are going to start
solver again and as you can see it kept the values from the previous solution. I am going
to go ahead and delete these so we can start all over. We need to set our objective which
is the same spot here to the value of 155 except this time the only thing we are changing
is the cell "B7" which was the original weight. Everything else remains the same so we are
not going to have to add any constraints. We can click "solve". Then you would have
to weigh 410 pounds on earth to weigh 155 pounds on Mercury.