Using Goal Seek to find two values
Using Goal Seek to find two values
(OP)
Hi,
I am trying to setup the attached excel file to find two values (Qd and K2, which I know the answers to be 665.1 and 122.3, respectively), that will give me a Teff = 2.75 s and Beff = 15%. I have done some research and believe the "Goal Seek" function in excel might be able to do this but have not been able to figure it out.
Appreciate if you guys can give me some guidance on using the "Goal Seek" function for my situation.
Thanks
I am trying to setup the attached excel file to find two values (Qd and K2, which I know the answers to be 665.1 and 122.3, respectively), that will give me a Teff = 2.75 s and Beff = 15%. I have done some research and believe the "Goal Seek" function in excel might be able to do this but have not been able to figure it out.
Appreciate if you guys can give me some guidance on using the "Goal Seek" function for my situation.
Thanks





RE: Using Goal Seek to find two values
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Using Goal Seek to find two values
You could (if you have some time on your hands, and you enjoy this sort of thing) solve it by using Goal Seek alternately on the two unknowns, but I just tried it and unless you are almost spot on with your starting values it is hopeless.
Solver on the other hand does a good job quite quickly in one step:
- Set up a cell with the sum of the absolute difference between the calculated values and target values (=ABS(B12-B4)+ABS(B15-B5))
- Open the Solver dialog, and set this cell as the Objective to minimise
- Select B8 and B9 as the Changing Variable Cells
- Click Solve
I get 663.9 and 122.1
As well as being much more powerful than Goal seek, Solver is often more convenient because it stores the query for re-use the next time, rather than having to re-enter it as you do with Goal seek.
You may have to enable Solver. It's hidden under File-Options-Addins, then click the Go button next to Manage Excel Add-ins
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using Goal Seek to find two values
1. Is the Set Target Cell, $B$8 and $B$9? Does that mean I have to use solver twice once for Qd and another time for K2?
2. Can't seem to find where I would use "Object to Minimize", not sure if its because I am using Excel 2007. Attached are some screen shots of what I have.
Really Appreciate your help.
RE: Using Goal Seek to find two values
The "Target Cell" in 2007 has become "Set Objective" in 2013
To do the optimisation in one operation create a formula summing the absolute error in both Teff and Betaeff, and select that cell as the "Target Cell" (cell b17 in my example).
It should work without changing anything else.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using Goal Seek to find two values
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Using Goal Seek to find two values
I tried following your approach, but got an error. I attached a word file showing what I am doing. Really appreciate your help in trying to figure this out.
Thanks
RE: Using Goal Seek to find two values
You can also enter constraints on the values it will use, but I didn't find that necessary in my brief testing.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using Goal Seek to find two values
I noticed that T_eff calculated equals to the 2.75, but the Beta_eff calculated does not equal 15%, it equals 0.7%.
Appreciate your help, in getting this figured out. Thanks
RE: Using Goal Seek to find two values
It works better if you use a weighted sum of the square of the errors as suggested by Greg (see attached), but in any case if an iterative solution converges to something that is clearly not the right answer, try a different starting point.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using Goal Seek to find two values
RE: Using Goal Seek to find two values
Thanks
RE: Using Goal Seek to find two values
=====================================
(2B)+(2B)' ?
RE: Using Goal Seek to find two values
I was actually experimenting with setting the target to a value (0), rather than a minimum, and I multiplied the error by 1E6, rather than going into the options and adjusting the constraint precision.
The number used is arbitrary, and it doesn't seem to make any difference when the target is set to minimum rather than a value, but it doesn't do any harm either.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using Goal Seek to find two values
Well, for starters it slows things down a bit.