Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do we use solver?
- Thread starter nqueen
- Start date
WalterContrata
Technical User
Nqueen,
First, make sure that you have the solver add-in.
Tools->Add-Ins
Check the box next to "Solver Add-in"
(This for the Office 2000 version of Excel, but I think that others are similar.)
Walter
First, make sure that you have the solver add-in.
Tools->Add-Ins
Check the box next to "Solver Add-in"
(This for the Office 2000 version of Excel, but I think that others are similar.)
Walter
WalterContrata
Technical User
Nqueen,
If it is not too much trouble, may I have some details about the formulae?
Best Regards,
Walter
If it is not too much trouble, may I have some details about the formulae?
Best Regards,
Walter
Try this link:
BlueHorizon
Instructor
Hi,
There are two similar functions, Solver and Goal Seek. Solver asks Excel to arrive at a certain conclusion and lets the user set parameters and restrictions, i.e., what cells to affect and what the final outcome should be.
Excel Help should be able to give you the specifics.
HTH,
Best,
Blue Horizon![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
There are two similar functions, Solver and Goal Seek. Solver asks Excel to arrive at a certain conclusion and lets the user set parameters and restrictions, i.e., what cells to affect and what the final outcome should be.
Excel Help should be able to give you the specifics.
HTH,
Best,
Blue Horizon
- Thread starter
- #6
I made sure that I had solver. I tried it yesterday but I am not sure how to do it. The thing is that my unkonwns are 3 exponents... My formulas are in this form:
X^a * Y^b * Z^c * R = T
M^a * N^b * U^c * R = I
E^a * F^b * G^c * R = H
where a b and c are the unkowns
So I set each variable in a cell and I put each formula in separate cell. I tried the solver but it does not work it gives me huge numbers while it is supposed to give me an exponent. any ideas?
by the way thanks zathras for that website it s very interesting but does not give me what I am searching for. I ll add it in my favorites!
X^a * Y^b * Z^c * R = T
M^a * N^b * U^c * R = I
E^a * F^b * G^c * R = H
where a b and c are the unkowns
So I set each variable in a cell and I put each formula in separate cell. I tried the solver but it does not work it gives me huge numbers while it is supposed to give me an exponent. any ideas?
by the way thanks zathras for that website it s very interesting but does not give me what I am searching for. I ll add it in my favorites!
- Thread starter
- #7
First, as I understand it, Solver provides a way for you to specify ONE input cell that will be fiddled until some other designated target cell reaches a desired value. So, with three unknowns, you are a bit out of luck.
Second, you can use logarithms to restate your formulas so that the standard techniques for three linear equations in three unkowns can be used.
In case you don't remember (or never learned) logarithms, here are the restated formulae:
[tt]
a * log(X) + b * log(Y) + c * log(Z) = log(T) - log(R)
a * log(M) + b * log(N) + c * log(U) = log(I) - log(R)
a * log(E) + b * log(F) + c * log(G) = log(H) - log(R)
[/tt]
You can use either natural or common logarithms. It doesn't matter.
If this is a one-time thing, you can use the web site cited above. If this is an on-going requirement, you can reverse-engineer the web site to see how the formulae work. Or there may be some third-party Excell add-in that makes working with simultaneous equations simple. I don't know of any, but you could do a search.
- Thread starter
- #9
Thanks Zathras and thanks to everyone for your help. I completly forgot about how to use logs and I am confused.
By the way, I forgot one thing in all formulas: it should be
((X^a * Y^b * Z^c )/P)* R = T (same for all three)
I guess the new formula for logs will be:
a * log(X) + b * log(Y) + c * log(Z) = log(T) - log(R) + log(P)
right?
Then if I use the formula you told me,Zathras, how do I use it in that website? I mean if the base is 10, log(T) will give me an answer. But what about a*logX? How do I find a? (that s the whole purpose of my forumla this is the unkown).
sorry for such stupid questions..
By the way, I forgot one thing in all formulas: it should be
((X^a * Y^b * Z^c )/P)* R = T (same for all three)
I guess the new formula for logs will be:
a * log(X) + b * log(Y) + c * log(Z) = log(T) - log(R) + log(P)
right?
Then if I use the formula you told me,Zathras, how do I use it in that website? I mean if the base is 10, log(T) will give me an answer. But what about a*logX? How do I find a? (that s the whole purpose of my forumla this is the unkown).
sorry for such stupid questions..
Your correction to include "P" appears to be right.
You will have to pre-compute the coefficients like log(T) so that you will have the proper numerical values for plugging into the website. Where the website is prompting[tt]
_________
Eqtn 1: a=[_________] plug in the value of log(X)
_________
b=[_________] plug in the value of log(Y)
_________
c=[_________] plug in the value of log(Z)
_________
d=[_________] plug in the value of log(P*T/R)
[/tt]
Eqtn 2 and 3 are similar. Then after you click Enter, your a, b, and c will be displayed where the captions x, y, and z are indicated.
You will have to pre-compute the coefficients like log(T) so that you will have the proper numerical values for plugging into the website. Where the website is prompting[tt]
_________
Eqtn 1: a=[_________] plug in the value of log(X)
_________
b=[_________] plug in the value of log(Y)
_________
c=[_________] plug in the value of log(Z)
_________
d=[_________] plug in the value of log(P*T/R)
[/tt]
Eqtn 2 and 3 are similar. Then after you click Enter, your a, b, and c will be displayed where the captions x, y, and z are indicated.
WalterContrata
Technical User
Nqueen,
This written before your recent exchange with Zathras. He is right, taking the logarithms of the equations is the way to go.
If you are still interested in the Solver, read on. Otherwise, please ignore.
Solver can solve systems with multiple unknowns. We can have multiple unkowns and equations, but we need to make a single "error" cell, a formula that gets smaller as we get closer to the solution. Are you game to try it?
It starts from an initial guess, and gives an approximate solution. Two cautions 1.) The approximation is usually very good, but with some problems it has trouble converging. 2.) If there are multiple solutions, it will only give one, and which one depends on the initial guess.
Step 1 Set-up unknown cells (By Changing Cells) and initial guess
Choose a range of 3 cells to hold your three unkowns, say,
A1 for a
B1 for b
C1 for c.
Put an initial guess for each cell, say, 1,1,1.
Step 2 Set-up an error cell (Target cell)
For each equation, set up a cell that contains the right hand side (RHS) minus the left hand side (LHS).
In your example, the formulae will be
= X^a * Y^b * Z^c * R - T
= M^a * N^b * U^c * R - I
= E^a * F^b * G^c * R - H
(But it may work better after taking the logs, as Zathra wrote.)
Let's say we put them in cells A3, A4, and A5.
Where the capital letter's are constants, and in place of a, b, and c, you put the cell references A1, B1, and C1, respectively.
If your guess in cells A1, B1, and C1 were a solution, all of these would be 0, right?
Now put the formula
= A3^2 + A4^2 + A5^2
in cell A6. The value of this cell will be zero when we get the solution.
Step 3 Run the Solver
Tools -> Solver
Fill in the list boxes.
Target Cell: $A$6 (error cell)
By Changing Cells: $A$1:$C$1 (unknown cells A, B, C)
Check the Equal To: Min radio button.
Push the solve button. Solver will replace your guess in $A$1:$C$1 by its approximation to the solution.
Walter
This written before your recent exchange with Zathras. He is right, taking the logarithms of the equations is the way to go.
If you are still interested in the Solver, read on. Otherwise, please ignore.
Solver can solve systems with multiple unknowns. We can have multiple unkowns and equations, but we need to make a single "error" cell, a formula that gets smaller as we get closer to the solution. Are you game to try it?
It starts from an initial guess, and gives an approximate solution. Two cautions 1.) The approximation is usually very good, but with some problems it has trouble converging. 2.) If there are multiple solutions, it will only give one, and which one depends on the initial guess.
Step 1 Set-up unknown cells (By Changing Cells) and initial guess
Choose a range of 3 cells to hold your three unkowns, say,
A1 for a
B1 for b
C1 for c.
Put an initial guess for each cell, say, 1,1,1.
Step 2 Set-up an error cell (Target cell)
For each equation, set up a cell that contains the right hand side (RHS) minus the left hand side (LHS).
In your example, the formulae will be
= X^a * Y^b * Z^c * R - T
= M^a * N^b * U^c * R - I
= E^a * F^b * G^c * R - H
(But it may work better after taking the logs, as Zathra wrote.)
Let's say we put them in cells A3, A4, and A5.
Where the capital letter's are constants, and in place of a, b, and c, you put the cell references A1, B1, and C1, respectively.
If your guess in cells A1, B1, and C1 were a solution, all of these would be 0, right?
Now put the formula
= A3^2 + A4^2 + A5^2
in cell A6. The value of this cell will be zero when we get the solution.
Step 3 Run the Solver
Tools -> Solver
Fill in the list boxes.
Target Cell: $A$6 (error cell)
By Changing Cells: $A$1:$C$1 (unknown cells A, B, C)
Check the Equal To: Min radio button.
Push the solve button. Solver will replace your guess in $A$1:$C$1 by its approximation to the solution.
Walter
WalterContrata
Technical User
Zathras,
Thanks. I like the solver, but your method was better in this case!
Thanks. I like the solver, but your method was better in this case!
If you go to
C:\Program Files\
Microsoft Office\Office\Samples\Solvsamp.xls
there is a well done example of the Solver program. I'm referencing Excel 2000 by the way.
The solver program is a nice way to do nonlinear optimization problems such as yours. When I got my Master's in Operations Research back in 1974, we had to do these by hand.
C:\Program Files\
Microsoft Office\Office\Samples\Solvsamp.xls
there is a well done example of the Solver program. I'm referencing Excel 2000 by the way.
The solver program is a nice way to do nonlinear optimization problems such as yours. When I got my Master's in Operations Research back in 1974, we had to do these by hand.
- Thread starter
- #15
Hi,
I tried to solve the formula by hand thanks to Zathras. I also tried to do it using the solver(in a slightly different way then WalterContracta was suggesting). The problem for both solutions is that it gives me negative exponents and I don t want that. In the sovler I tried to click on the "assume non negative" in the solver's options and then it gave me the error message saying "solver ocund not find a feasible solution". Is there a way to avoid the negative results?
I tried to solve the formula by hand thanks to Zathras. I also tried to do it using the solver(in a slightly different way then WalterContracta was suggesting). The problem for both solutions is that it gives me negative exponents and I don t want that. In the sovler I tried to click on the "assume non negative" in the solver's options and then it gave me the error message saying "solver ocund not find a feasible solution". Is there a way to avoid the negative results?
If you post the actual numbers you are working with, perhaps I or someone can come up with an answer. But if the only way to solve the set of equations is with negative exponents, you may just have to live with that.
Contrariwise, if you know that negative exponents are wrong, then there must be something wrong with one or more of the other constants.
WalterContrata
Technical User
nqueen,
Maybe the solution with negative exponents is correct. Is it unthinkable?
After taking the logarithms (like Zathras posted), you have 3 linear equations with 3 unknowns. There are 3 possibilities:
[ul]
[li]there is just 1 solution. (Yeah! Good problem.)[/li]
[li]there are no solutions. (@#!)[/li]
[li]there are infinite number of solutions (@#!)[/li]
[/ul]
Would it be too much trouble to post the coefficients and constants in your problem?
Walter
Maybe the solution with negative exponents is correct. Is it unthinkable?
After taking the logarithms (like Zathras posted), you have 3 linear equations with 3 unknowns. There are 3 possibilities:
[ul]
[li]there is just 1 solution. (Yeah! Good problem.)[/li]
[li]there are no solutions. (@#!)[/li]
[li]there are infinite number of solutions (@#!)[/li]
[/ul]
Would it be too much trouble to post the coefficients and constants in your problem?
Walter
- Thread starter
- #18
The thing is that the formulas I gave u is only a small piece of the pie! These equations, are the easiest because they have 3 unkowns with three equations. I have the same formulas' format (with three unkowns but 5,6 sometimes 10 equations). That s why I am trying to use the solver more than the forumla by hand.
Ill give you the numbers of the equations cited in the thread above:
((199^a * 1248^b * 377^c )/2956139 )*1.0=449
((248*1546^b*407^c)/4118256)*0.45=218
((319^a*1426^b*455^c)/4081782)*0.35=113
and to answer your question zathras, I must have positive exponents.
and thanks again for your help guys!
the total of the three equations = 780 (449+218+113)
Ill give you the numbers of the equations cited in the thread above:
((199^a * 1248^b * 377^c )/2956139 )*1.0=449
((248*1546^b*407^c)/4118256)*0.45=218
((319^a*1426^b*455^c)/4081782)*0.35=113
and to answer your question zathras, I must have positive exponents.
and thanks again for your help guys!
the total of the three equations = 780 (449+218+113)
- Thread starter
- #19
Allowing for the typo in your second equation, plugging in your results appears to give consistent answers.
So, either your measurements are off, or you are using the wrong formulas, or I don't know what. Are you using consistent dimensions for your values? Those large divisors look suspicious.
You say you have 10 equations with three unknowns. If all 10 are consistent then any three taken at random should give you the same solution. Have you tried another set of values (of the 10 sets)?
- Status
Similar threads
- Locked
- Question