Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2005
    Posts
    45

    Unanswered: Is there no GoalSeek or Solver in Access?

    I need to perform one of these functions in access vba but it doesn't look like they are available.

    Does anyone have any ideas?

    Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It might help if you included a little blurb on what those functions do...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    45
    Sorry.

    Goalseek: Given the formula '15=9x + 12', goalseek will return the value of x that completes the formula. '15' and '=9x+12' are the inputs.

    Solver is a more advanced form of goalseek, using multiple inputs and constraints.

    I really am just looking for a function or set of code that would duplicate goalseek. I'm guessing a basic loop with comparisons might work.

    THanks!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is your practical application for using this function?

    There's no inline vba function that will do it for you that I know of. Might be kind of fun to whip one up though...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    GoalSeek or Solver

    Is that a Excel thing

    The only work a round I can come up with is

    get msaccess to open excel and full the cells then
    get msaccess to read back the answer form the excel file
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's not fun at all myle

    HeavyBall - it might be an idea to post your precise formulae (or a good representative sample). I would imagine solving the equation you supplied would be fairly trivial but I have a suspicion that you have much bigger fish to fry....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello HaeavyBall!

    If I can include in this matter.
    Look at "DemoGoalSeekA2000".
    Open Form1, look at Module1.
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2005
    Posts
    45
    Here's what I came up with yesterday. Its an approximation, but seems to do the job. The incremental change and tolerance can be adjusted depending upon need:

    Sub test()

    Dim x, y As Single

    y = 3.22
    x = 0

    Do
    If Abs(Round(2 * (x ^ 2) + x - 7, 2) - y) < 0.1 Then
    Exit Do
    ElseIf 2 * (x ^ 2) + x - 7 < y Then
    x = x + 0.01
    ElseIf 2 * (x ^ 2) + x - 7 > y Then
    x = x - 0.01
    End If
    Loop

    Debug.Print x

    End Sub

  9. #9
    Join Date
    Dec 2005
    Posts
    45
    I guess this is as much a finance question as anything. We use goalseek in excel to calculate a price given a required return and IRR, and I am just trying to duplicate that calculation in Access.

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Looks to me like you are trying to solve the quadratic equation:

    2 * (x ^ 2) + x - 7 = y

    The solution for this will have two values for x (because it's a quadratic). You have presented an iterative method for solving such and equation, however, you do not know which solution for x you will get as the answer.

    Besides, a quadratic equation can always be solved by formula to give both solutions for x which means you can do all this in a query.

    Check out this

    Note that your have to use a value of c=(-7-y) as you need to make the r.h.s. equal zero.

    hth
    Chris

  11. #11
    Join Date
    Nov 2002
    Location
    Sydney, Australia
    Posts
    11
    I feel that Myle's suggest may really be the most practical. Unless you have only one scenario that you need solved - for which you could derive the code - you'd be better served using Excel itself (via code), as this will give you access to all of Excel's functionality.
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au
    Australia

  12. #12
    Join Date
    Dec 2005
    Posts
    45
    Thanks for all the help!

    Firstly, I grossly oversimplified what I am trying to do. The best solution for my specific problem would be code that can perform the following with the fewest possible loops. Also, b is a constant, while a is derived from a very long process, so assume a is a random number:

    a = 10.254
    b = 30

    do
    if a < b then
    a = a + .001
    elseif a > b then
    a = a - .001
    else 'a = b
    debug.print a
    exit do
    end if
    loop

    The main challenge here being a way to adjust the increment (.001) to minimize loops.
    Last edited by HeavyBall; 05-29-06 at 13:03.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does something like the search-in-array idea work?

    something like:

    c= a/2
    do while a<>b
    if a>b then
    a = a-c
    else
    a = a+c
    endif
    if (a - b)^2 < c^2 then
    c=c/2
    endif
    loop

    as ever - totally untested air-code

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or maybe better:

    c= a/2
    do while a<>b
    if a>b then
    a = a-c
    else
    a = a+c
    endif
    if a=b then exit loop
    if (a - b)^2 < c^2 then
    c=c/2
    endif
    loop
    currently using SS 2008R2

  15. #15
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    This is similar (but not the same i don't think) as izys suggestion. The point is you half the gap on each iteration

    do
    if a>b+tolerence or a<b-tolerence then
    a = (a + b)/2
    else
    debug.print a
    exit do
    end if
    loop

    This will get to within 0.01 in about 11 interations using your given example data. I think it will work regardless of the sign of and b.

    What kind of formula are you trying to solve ? There are methods for trying to solve different kinds of problems. Are you using a similar formula each time? Is it a polynomial or something more complex ?

    hth
    Chris

Posting Permissions

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