Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel 2007 Goal Seek Formula

Posted on 2010-11-17
Medium Priority
1,323 Views
Hello,

Please review the attached code.  The goal seek formula is making the adjustment in cell E64.  I need to set up a limit in which the amount in E64 that goal seek recommends should not be less than 0 or greater than the amount in cell I64.  If the amount does not fall within this parameter an error message appears stating "Amount Does Not Fall Within Required Parameters.  Please Try Again".

Any suggestions on how to make this happen?

Thanks!

``````Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")
``````
0
Question by:Escanaba
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 37

Expert Comment

ID: 34158648
Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)
0

LVL 1

Author Comment

ID: 34158818
So for the entire string its this:
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)

If so its bugging stating reference not valid.
0

LVL 1

Author Comment

ID: 34158847
Here is the full code:

Private Sub CommandButton2_Click()
With Application
.EnableEvents = False
.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End With

Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)

With Application
.EnableEvents = True
.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End With

End Sub
0

LVL 37

Expert Comment

ID: 34158894
No. My code was for after the value was put in E64. Like this: (You could use a different cell for the final value)
``````Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")

Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)
``````
0

LVL 81

Expert Comment

ID: 34158910
Solver can handle multiple constraints and changing cells, but Goal Seek requires a single criterion (minimum, maximum or specific value).

From the description of the problem, the final solution might be:
=MAX(0,MIN(I64,E64))                 the Goal Seek result should be between 0 and I64.

You would then run Goal Seek using E64 same as previously, then replace the value found by Goal Seek with the result of the MAX/MIN formula.
0

LVL 37

Expert Comment

ID: 34158945
byundt, your answer is the same as mine except the median function does the min/max in one step.
0

LVL 1

Author Comment

ID: 34159037
Got it & thank you.  Any recommendations on the 2nd part of my original question regarding generating an error message so the end user knows they've exceeded the parameters?  As it stands, nothing updates which is good but doesnt tell them why.
0

LVL 37

Accepted Solution

TommySzalapski earned 2000 total points
ID: 34159083
Then you'd need something like this (and the median trick isn't as useful).
``````Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")

If Range("E64").Value <0 Then
MsgBox "Came out less than 0, changed to 0"
Range("E64").Value = 0
End if
If Range("E64").Value > Range("I64").Value Then
MsgBox "Came out greater than " & Range("I64").Value & ", changed to " & Range("I64").Value
Range("E64").Value = Range("I64").Value
End If
``````
0

LVL 1

Author Closing Comment

ID: 34159162
Appreciate the assistance.  Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll