+ Reply to Thread
Results 1 to 7 of 7

Goal Seek as a function?

  1. #1
    Registered User
    Join Date
    09-15-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Goal Seek as a function?

    Hi guys,

    I'm new to Excel VBA/Macros. What I'd like to do is to use a function like this one: =goalseek(set_cell,to_value)

    and the cell that I write this in will be the "By changing cell", and the other two ranges will be 'to_value' and 'set_cell'.

    Does this make sense?

    Thanks

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,190

    Re: Goal Seek as a function?

    I don't think you are going to get a "user-defined function" (UDF) to work this way. A UDF cannot call the goal seek utility and get it to run. Entering a UDF into the "set cell" would also replace the formula in that set cell, which I assume is the formula you would like to "find the root" of.

    I think the closest thing to what you describe would be a change and/or calculate event procedure that invokes goal seek when the spreadsheet changes or calculates.

    When I have wanted to do this, I have written my own root finding algorithm for the problem in VBA. (https://en.wikipedia.org/wiki/Root-finding_algorithm ) Usually I use the Newton Raphson method, but I have also used bisection or secant methods as well.

    I don't know if it would apply to your specific problem, but I see a lot of problems like this where the user was just unwilling to solve the problem algebraically. I realize that algebra cannot solve all problems, but it is a little surprising to me how many of these problems would be easiest to solve if the user would dust off the algebra they learned way back in school.

    I'm not sure we can say much more than that without a more detailed description of the problem you are trying to solve.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    09-15-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Goal Seek as a function?

    Quote Originally Posted by MrShorty View Post
    I don't think you are going to get a "user-defined function" (UDF) to work this way. A UDF cannot call the goal seek utility and get it to run. Entering a UDF into the "set cell" would also replace the formula in that set cell, which I assume is the formula you would like to "find the root" of.

    I think the closest thing to what you describe would be a change and/or calculate event procedure that invokes goal seek when the spreadsheet changes or calculates.

    When I have wanted to do this, I have written my own root finding algorithm for the problem in VBA. (https://en.wikipedia.org/wiki/Root-finding_algorithm ) Usually I use the Newton Raphson method, but I have also used bisection or secant methods as well.

    I don't know if it would apply to your specific problem, but I see a lot of problems like this where the user was just unwilling to solve the problem algebraically. I realize that algebra cannot solve all problems, but it is a little surprising to me how many of these problems would be easiest to solve if the user would dust off the algebra they learned way back in school.

    I'm not sure we can say much more than that without a more detailed description of the problem you are trying to solve.
    Thanks for your reply! Well.. can I have a simple solution like this:

    Col 1: "By changing cell" Col 2: "Set Value" Col 3: "Value To" Col 4: "GoalSeek function which finds the needed value for 'by changing cell' to make 'set value' equal to 'value to'"
    1 31 30 GoalSeek Result
    2 32 30 GoalSeek Result
    3 33 30 GoalSeek Result

    EDIT: My actual formula is way too long and convoluted to do reverse algebra on.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,190

    Re: Goal Seek as a function?

    I cannot think of any "built in" or "preprogrammed" utilities in Excel for this sort of thing.

    EDIT: My actual formula is way too long and convoluted to do reverse algebra on.
    I suppose we have to take your word for this, though I would point out that the programming exercise to automate goal seek is not a trivial exercise, either. Somewhere in this you need to decide which approach is going to take more effort -- reverse algebra or programming a root finding algorithm.

    Since you have not shared anything about your formula, it is difficult for me to make a firm suggestion. It is rarely my first choice, but I might suggest an approach like this:

    1) Put a formula in Col 4 -- =col2-col3
    2) Record a Macro of you
    a) calling goal seek
    b) tell goal seek to set col 4 to a value of 0 by changing col 1
    3) Take the resulting recorded code, adapt it and put it inside of a For..Next or Do..Loop that will step through the rows.
    4) Decide whether to associate this code with a change or calculate event or a button or other method of calling this macro.
    5) Test and debug to make sure it is working.

    Does that sound like a workable approach, or do you have something else in mind?

  5. #5
    Registered User
    Join Date
    09-15-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Goal Seek as a function?

    Quote Originally Posted by MrShorty View Post
    I cannot think of any "built in" or "preprogrammed" utilities in Excel for this sort of thing.

    I suppose we have to take your word for this, though I would point out that the programming exercise to automate goal seek is not a trivial exercise, either. Somewhere in this you need to decide which approach is going to take more effort -- reverse algebra or programming a root finding algorithm.

    Since you have not shared anything about your formula, it is difficult for me to make a firm suggestion. It is rarely my first choice, but I might suggest an approach like this:

    1) Put a formula in Col 4 -- =col2-col3
    2) Record a Macro of you
    a) calling goal seek
    b) tell goal seek to set col 4 to a value of 0 by changing col 1
    3) Take the resulting recorded code, adapt it and put it inside of a For..Next or Do..Loop that will step through the rows.
    4) Decide whether to associate this code with a change or calculate event or a button or other method of calling this macro.
    5) Test and debug to make sure it is working.

    Does that sound like a workable approach, or do you have something else in mind?
    That sounds exactly it. I just need your help with steps 3 to 5. Here's my macro code:

    Sub GS()
    '
    ' GS Macro
    '
    ' Keyboard Shortcut: Ctrl+j
    '
    ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(0, -1).Range( _
    "A1")
    End Sub


    How do I do the for next or do so that it automatically applies to all rows? at present, I have to do ctrl+j for every cell/row.

    Thanks very much btw!
    Last edited by valeous; 11-12-2015 at 07:39 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,190

    Re: Goal Seek as a function?

    You will need to become familiar with a For..Next Loop (https://msdn.microsoft.com/EN-US/lib.../gg251601.aspx ). You will also need to become familiar with the Range object in Excel: https://msdn.microsoft.com/EN-US/lib.../ff838238.aspx

    1) Identify the desired range that you are working with. For example Range("A1:D5") or Range("M24:P32") or whatever it happens to be. It might be easiest to use a Set statement to set an object variable to this range: Set goalrange=Activesheet.range("G44:J55")
    https://msdn.microsoft.com/EN-US/lib.../gg251642.aspx
    2) Set up the loop: For i=1 to 12
    3) replace "activecell" with an actual reference to the desired cell goalrange.cells(i,4).goalseek goal:=0, changingcell:=goalrange.cells(i,1)
    https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx
    4) Increment loop and repeat: Next i
    5) End

    I would expect something like this (untested pseudocode):
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-15-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Goal Seek as a function?

    Quote Originally Posted by MrShorty View Post
    You will need to become familiar with a For..Next Loop (https://msdn.microsoft.com/EN-US/lib.../gg251601.aspx ). You will also need to become familiar with the Range object in Excel: https://msdn.microsoft.com/EN-US/lib.../ff838238.aspx

    1) Identify the desired range that you are working with. For example Range("A1:D5") or Range("M24:P32") or whatever it happens to be. It might be easiest to use a Set statement to set an object variable to this range: Set goalrange=Activesheet.range("G44:J55")
    https://msdn.microsoft.com/EN-US/lib.../gg251642.aspx
    2) Set up the loop: For i=1 to 12
    3) replace "activecell" with an actual reference to the desired cell goalrange.cells(i,4).goalseek goal:=0, changingcell:=goalrange.cells(i,1)
    https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx
    4) Increment loop and repeat: Next i
    5) End

    I would expect something like this (untested pseudocode):
    Please Login or Register  to view this content.
    Works like an absolute charm! Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1