Results 1 to 9 of 9
Thread: Goal Seeking ???

20120321, 21:38 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,469
 Thanks
 30
 Thanked 61 Times in 57 Posts
Goal Seeking ???
I have a user's spreadsheet that calculates various formulas and one cell eventually has: =AND(F57<H57,D60<F60,D61<F61)
One of the constants in the spreadsheet (in E21) drives other formulas which eventually produce a 1 for this AND condition.
I want to find the maximum value for the cell with the constant that makes the AND condition TRUE.
I can't seem to do this with Goal Seeking. Maybe it's not that.
Maybe it's a VBA loop (I don't know VBA) that starts with a high guess or maybe a high and low range and works through the range until it finds the max value that makes it TRUE.
Does this make sense? And, if so, maybe someone sees a way to do this... GREAT!!!
As I think more on this...maybe the VBA should have a high and low and an increment (e.g., 1 [change by integers], or 0.1 [change by tenths).Last edited by kweaver; 20120321 at 21:44.

20120322, 05:51 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
This should be doable with the builtin SOLVER addin...
Steve

20120322, 10:26 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,469
 Thanks
 30
 Thanked 61 Times in 57 Posts
It seems that the SOLVER would be the choice. But I can't understanding from the dialog box nor the explanation how to specifically set it.
I have three constraints (F57<H57, D60<F60 and D61<F61) and I need to maximize cell E21, but that cell is a constant, not a formula.
So, I'm confused as to where to reference that cell. I keep getting error boxes when trying what my pea brain thinks are the settings.

20120322, 21:26 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If E21 is a constant how do you expect it to be maximized? It will not change no matter what happens in other cells. You need to have a cell formula that changes with the cells you are specifying will change, with the given constraints.
Steve

20120323, 10:42 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,469
 Thanks
 30
 Thanked 61 Times in 57 Posts
So, Steve (or anyone else), do you think a VB program with a counter for the E21 constant cell could solve this problem for me?
Maybe the UDF would have a high, low and increment to decrease by from the high, until the three criteria are met or never reach true?

20120323, 15:19 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The question remains what is the dependent cell that you want to optimize. E21 is an independent value. You have provided information on an independent cell and some contraints, but what is the dependent cell you want "optimized"? For interation, solver works, though often a little algebra can solve it directly if there is a particular answer.
Could you elaborate on the problem in more detail?
Steve

20120323, 17:01 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,469
 Thanks
 30
 Thanked 61 Times in 57 Posts
My user is an engineer and the constant value in E21 is used in various calculations (along with other values). He's trying to find out what the maximum value could be that he could enter in E21 that would make the three conditions (F57<H57, D60<F60 and D61<F61) true. So, he's been changing E21 manually from, say, 100 to 101 to 102, or 120 to 119, etc. and when the various calculations are done and the 3 conditions are met, he uses the resulting E21 value elsewhere. Because, as you said, Steve, this is an independent value, I'm at a loss to try to come up with a way to automate the process. That's why I thought a UDF that sets E21 and that starts at some given number and decreases by some increment (1 or .5, etc.) down to a lower bound until the conditions are met might be the answer...but, maybe this isn't possible because of the independency nature of E21.

20120323, 18:55 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
This should do what you want. Just set the range for the min, max, and the interval that E21 must be in and run the code. It starts with the MAx value entered and loops until the min decrementing by the interval. Once the condition is met it stops. If the code gets to the minimum, it pops up a message.
Steve
Code:Option Explicit Sub FindMaxE21() Dim dMin As Double Dim dMax As Double Dim dInt As Double Dim x As Double 'Set these Values dMin = 10 dMax = 500 dInt = 0.2 For x = dMax To dMin Step dInt Range("E21") = x ActiveSheet.Calculate If Range("F57") < Range("H57") And _ Range("D60") < Range("F60") And _ Range("D61") < Range("F61") Then _ Exit Sub Next MsgBox "No Values in range met conditions" End Sub

20120327, 22:25 #9
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,469
 Thanks
 30
 Thanked 61 Times in 57 Posts
That's perfect. Thanks.