Solved

Excel 2007 Goal Seek Formula

Posted on 2010-11-17
9
1,242 Views
Last Modified: 2012-06-27
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")

Open in new window

0
Comment
Question by:Escanaba
  • 4
  • 4
9 Comments
 
LVL 37

Expert Comment

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

Author Comment

by:Escanaba
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

by:Escanaba
ID: 34158847
Here is the full code:

Private Sub CommandButton2_Click()
    With Application
       .DisplayAlerts = False
       .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
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End With
 
End Sub
0
 
LVL 37

Expert Comment

by:TommySzalapski
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)

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 81

Expert Comment

by:byundt
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

by:TommySzalapski
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

by:Escanaba
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

by:
TommySzalapski earned 500 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

Open in new window

0
 
LVL 1

Author Closing Comment

by:Escanaba
ID: 34159162
Appreciate the assistance.  Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now