INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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




RE: Using Goal Seek to find two values

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/

RE: Using Goal Seek to find two values

(OP)
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.

RE: Using Goal Seek to find two values

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/

RE: Using Goal Seek to find two values

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/

RE: Using Goal Seek to find two values

(OP)
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

RE: Using Goal Seek to find two values

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/

RE: Using Goal Seek to find two values

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

RE: Using Goal Seek to find two values

(OP)
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

RE: Using Goal Seek to find two values

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)' ?

RE: Using Goal Seek to find two values

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/

RE: Using Goal Seek to find two values

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close