Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    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; 2012-03-21 at 21:44.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Bronze Lounger
    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Bronze Lounger
    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Bronze Lounger
    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    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
  •