×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Goalseeking to a minimum vlaue
3

Goalseeking to a minimum vlaue

Goalseeking to a minimum vlaue

(OP)
Wonder if you could all help.

I have a spreadsheet working at the moment which i want to run a goalseek on, problem is I dont know the value i want, it just want the minimum for a given range of X. I can think of a very messy way of doing this using loops and if statements,and changing the x value between the range in small increments and storing the minimum value of y and its corresponding x, but that all seems rather messy.

Is there a clean way to do this?

Thanks

RE: Goalseeking to a minimum vlaue

1)  Use the MIN() function

2)  Use the MATCH(), VLOOKUP(), HLOOKUP() functions

RE: Goalseeking to a minimum vlaue

3
It sounds like a real simple VBA routine if none of the built in functions are viable. I have done this with complex equations, all of which had to meet certain criteria, to come up with the best solution. It sounds like you just want to modify X within a range to find the lowest Y value. Another option would be to pull out the ol' integration techniques...

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Goalseeking to a minimum vlaue

Instead of goal seek, you may want to use the solver. It allows you to specify a minimize function.

RE: Goalseeking to a minimum vlaue

Goalseek won't work to a minimum, as I remember.

Solver will.

Your 'messy' method might be the best approach, if you can examine the entire solution space in a reasonably short time, since it is the only pproach that is guaranteed to find the true minimum.

If that is not feasible you might want to loo at genetic algorithms.

I have used "genetik201" with some success for this, although I have now written my own.

Cheers

Greg Locock

RE: Goalseeking to a minimum vlaue

Jkaen,
If you want to try a VBA routine, give me some specs (cell numbers) and I can whip up a quick routine.

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Goalseeking to a minimum vlaue

(OP)
Thanks for the help guys.

I have a few deadlines on other projects at the moment, once those are up and i have a bit of time I will send you the specs, thanks.

RE: Goalseeking to a minimum vlaue

(OP)
I tried to email you the information, but your spam filter blocked it, I will post the info here instead:

Don't know exactly the details you need, so if I miss anything please let me
know.

I am varying cell B26 from 0.1 to 1 in steps of 0.05. at each step I want to
run the macro 'setgl' and the output I am interested in minimising is
displayed in cell F32. I am looking to store the minimum F32 value in cell
J23, and its corresponding B26 value in cell J24.

Is this enough data for you?

Thanks for this help I appreciate it.

RE: Goalseeking to a minimum vlaue

Give this a try:

Sub FindMinimum()
    Dim i1 As Integer
    Dim r1 As Double, r2 As Double, r3 As Double
    r2 = 1000000
    For i1 = 10 To 100 Step 5
        r1 = CDbl(i1 / 100)
        Range("B26").FormulaR1C1 = r1
        Call setgl
        If Range("F32").Value < r2 Then
            r2 = Range("F32").Value
            r3 = r1
        End If
    Next i1
    Range("J23").FormulaR1C1 = r2
    Range("J24").FormulaR1C1 = r3
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Goalseeking to a minimum vlaue

A couple of things I should have mentioned:

The minimum value must be less than 1,000,000.
When run, the input/output sheet must be the active sheet.

If you want greater accuracy (smaller steps), just change the following line. Everything is multipled by 100 to utilize integers.

For i1 = 10 To 100 Step 5
If you want a 0.01 step, change 5 to 1.


DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Goalseeking to a minimum vlaue

(OP)
That works great, thanks for all the help.

RE: Goalseeking to a minimum vlaue

DSI:

Thanks for your code. I used it as a jumping-off point for a similar problem of my own.

One question, though: What is the function of your "Call setgl" statement? I've searched the VBA help file and found no reference to it and I'm just curious.

Thanks in advance.

RE: Goalseeking to a minimum vlaue

If you refer to Jkaen's post, you'll see it is the macro he wanted to run between calculations. I can only presume that the automatic sheet recalculation did not trigger his custom calculations, requiring it to be run for each step.

Glad to help...

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Goalseeking to a minimum vlaue

(OP)
dsi is correct, the setgl macro solves an itterative calculation at each step change

RE: Goalseeking to a minimum vlaue

I don't know why the people who mentioned solver have down-played it. I use solver all the time with excellent results. Load it from the tools/add-ins menu.

It is iterative. The solutions are not true minimums in the sense of a mathematical root, but they are very close approximations to a minimum, good enough for engineering use. Sometimes it does not find a solution, but I can usually put a different starting value in the solution cell and make it work.

Solver also lets me define constraint conditions. Sometimes I use solver to solve a set of simulataneous equations, where I don't really care that the value is a "mimimum" but only that all the constraints (simul. equ) are satisfied.

RE: Goalseeking to a minimum vlaue

"The solutions are not true minimums in the sense of a mathematical root, but they are very close approximations to a minimum, good enough for engineering use."

Sorry, not true.

It is good enough for smoothly varying functions, but fails dismally on many examples.

For instance, I have a spreadsheet that calculates the fuel consumption of a car with an automatic gearbox. I would like to optimise the gear ratios of the box, and the speed and throttle opening at which it changes gear.

Solver does not even begin to cope with this case, which has  only (roughly) 10 input variables and one output variable.

Another case where it failed dismally was in calculating the optimum speed strategy for a solar car in variable weather.

I am pleased when it works, but do not rely on it working.

Cheers

Greg Locock

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