Goalseeking to a minimum vlaue 3

[Jkaen]

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

 

[melone]

1) Use the MIN() function

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

 

[dsi]

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.

 

[MikeClay]

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

 

[GregLocock]

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

 

[dsi]

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.

 

[Jkaen]

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.

 

[Jkaen]

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.

 

[dsi]

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(&quot;F32&quot;).Value < r2 Then
            r2 = Range(&quot;F32&quot;).Value
            r3 = r1
        End If
    Next i1
    Range(&quot;J23&quot;).FormulaR1C1 = r2
    Range(&quot;J24&quot;).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.

 

[dsi]

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.

 

[Jkaen]

That works great, thanks for all the help.

 

[Binary]

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.

 

[dsi]

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.

 

[Jkaen]

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

 

[jabaldauf]

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.

 

[GregLocock]

"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