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
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
2) Use the MATCH(), VLOOKUP(), HLOOKUP() functions
RE: Goalseeking to a minimum vlaue
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
RE: Goalseeking to a minimum vlaue
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
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
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
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
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
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
RE: Goalseeking to a minimum vlaue
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
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
RE: Goalseeking to a minimum vlaue
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
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