1. ## 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).

2. This should be doable with the builtin SOLVER addin...

Steve

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

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

5. 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?

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

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

8. 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```

9. That's perfect. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•