Using Goal Seek to find two values

[CWEngineer]

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

 

[GregLocock]

No, goal seek only optimises one cell, you need to use solver.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IDS]

Greg just said what I was going to say, so I'll add a bit more detail:

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/

 

[CWEngineer]

Thanks for your help IDS. I had a few more questions in trying to use the Solver:

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.

 

[IDS]

See the attached screen shot.

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/

 

[GregLocock]

You may find that the weighted sum of the squares of the errors is a more useful objective in general.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[CWEngineer]

Hi,

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

 

[IDS]

You just need to enter some non-zero values in the cells to be adjusted, so there are no errors when you start off.

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/

 

[CWEngineer]

I entered a value of 20 for Qd and a value of 30 for K2, but I did not get the answer (please see attach document, which shows my process).

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

 

[IDS]

It looks like there is a local minimum which you hit if you start so far from the true result.

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/

 

[CWEngineer]

Thank you so much for your help, got it working!

 

[CWEngineer]

One more question, the last excel spreadsheet has (=(B18+B17)*100,0000). Was curious what the 100,0000 value is for. Will this value always be the same for every solver operation OR is it the decimals place, the values (T_eff and Beta_eff) will be correct to?

Thanks

 

[electricpete]

I think multiplying your objective function by 1E6 only adjusts the stoping criteria... you could do the same thing by multiplying your convergence limit by 1E-6. Otherwise, shouldn't make much difference that I can think of.

=====================================
(2B)+(2B)' ?

 

[IDS]

Pete has it right.

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/

 

[Denial]

"It doesn't do any harm either."
Well, for starters it slows things down a bit.