Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Math problem (Access 2002 XP)

    Hi everybody:

    This is too easy not to work, but here goes!

    I'm doing some simple math in VBA code using doubles. I have:
    dblResult = dblCalc ^ dblPower

    where (in this case) dblCalc is a small negative number (-0.121) and dblPower is a small positive number (0.049).
    If I type these values in the Immediate window, I get the answer:
    ?-0.121 ^ 0.049
    -0.901688401093072
    But when I type the calculation using the variables, I get "Runtime error 5: Invalid procedure call or argument."
    ?dblcalc
    -0.121
    ?dblpower
    0.049
    ?dblcalc ^ dblpower

    If I try doing this in sql, the cell has "error" in it. Sometimes, I get a different error in sql: "access automation error."

    Any idea what is going on?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Math problem (Access 2002 XP)

    The result in the immediate window is wrong. The outcome of -0.121 ^ 0.049 is not a real number (it is a complex number), so the error message you get is correct.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Hi Hans:

    Could you please explain that? (I am not a mathematician, "just" a programmer.) I thought the "wrong" answer in the Immediate window looked correct. It came out to a little over 9%.
    Also, how should I go about getting the "real" answer? I am trying to calculate the annualized average return of a fund as a percent. Basically, it goes:

    dblCalc = ((1+Nz(mo1))*(1+Nz(mo2))*(1+Nz(mo3)).......
    dblPower = TotalMonths/12
    dblResult (dblCalc ^ dblPower) -1

    This works most of the time, but the numbers are small due to being percentages expressed as decimals.
    When I do it in Excel's UI, it works okay (each cell holds the average cumulative return for the year as a percent, i.e. 0.1014 = 10.14%):

    =((1+B2)*(1+C2)*(1+D2)*(1+E2)*(1+F2)*(1+G2)*(1+H2) *(1+I2)*(1+J2)*(1+K2)*(1+L2)*(1+M2)*(1+N2)*(1+O2)* (1+P2)*(1+Q2)*(1+R2)*(1+S2)*(1+T2)*(1+U2)*(1+V2))^ (12/246)-1

    but, of course, this is an Access app. How should I do the calc in vba?

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Kathryn:

    Have you looked at the VBA Financial Functions? My understanding of average rates of return is that my interest is less than it should be. You might be able to use one of the VBA functions rather than possibly re-inventing the wheel.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Math problem (Access 2002 XP)

    In addition to FlyerMike's replies (thanks, Mike!), it may be useful to know that many of Excel's financial functions are built into VBA. I'm the opposite of a financial expert (Lounge pay is a very stable and predictable amount: zero), so I cannot really help here, but perhaps you can use the IRR or MIRR functions.

    BTW, the reason that you can calculate -0.121 ^ 0.049 in the Immediate window is that it is interpreted as -(0.121 ^ 0.049). If you try to calculate (-0.121) ^ 0.049, you'll get the same error message you got using variables.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    The immediate window doesn't exactly handle this particular math problem well, or accurately.

    For example, 4 to the power of 0.5 is really asking for the square root of 4 --> 2. I heard a "duh" from the peanut gallery.

    In the immediate window,

    ? 4 ^ 0.5
    2

    So far, so good.

    ? (-4 ^ 0.5)
    -2 ...bzzzt.

    You can't get the square of a negative number.....it is, as Hans stated, an imaginary number. (2 * i), where "i" is the square root of -1.

    The same explanation holds for some negative number raised to the power of a number less than 1.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Hi Hans and FlyerMike:

    Thank you for your explanations, which went right over my head. My math career ended in high school in the middle of analytic geometry, when the sexism was so thick you could cut it with a knife, and we girls made a quick escape. That was 1963. Now, I just write code.

    Can either of you math whizzes answer my question: how can I get this calc (which works fine in Excel) to work in Access VBA? Unless you're telling me that Excel is making an error and not alerting me?

    Still clueless,

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Would you mind posting your Excel file, with any confidential data removed? The attachment needs to be less than 100 kb.

    http://www.wopr.com/cgi-bin/w3t/faq_...l?Cat=#attach"

    Rules for attachments

    BTW, I was thrown out of math classes in the '70s.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    It doesn't work in Excel for me.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Kathryn:

    Here's my best guess. Attached is an Excel file that appears to mimic your explanation. I also included an Access file with a small table and a function appropriately titled "HopeThisWorks". As you will see I built a table of dates (months) and interest rates. I made no attempt to validate that interest rates will be between 0 and 1.

    The following function is in the module:

    <pre>Public Function HopeThisWorks() As Double
    On Error GoTo Err_HopeThisWorks

    Dim dMonths As Double, dSubtotal As Double, dResult As Double
    Dim MyRS As DAO.Recordset

    Set MyRS = CurrentDb.OpenRecordset("tbl_Data")

    dSubtotal = 1
    'Gather the data points
    'For each new piece of data, add 1 to it and multiply by the dSubtotal
    With MyRS
    If .RecordCount Then
    .MoveFirst
    While Not .EOF
    If Nz(.Fields("MyMonth").Value, 0) Then
    dMonths = dMonths + 1
    dSubtotal = dSubtotal * (1 + Nz(.Fields("InterestRate").Value, 0))
    End If
    .MoveNext
    Wend
    End If
    .Close
    End With

    If dMonths Then
    dResult = (dMonths / 12) ^ dSubtotal - 1
    End If


    Exit_HopeThisWorks:
    Set MyRS = Nothing
    HopeThisWorks = dResult
    Exit Function

    Err_HopeThisWorks:
    MsgBox "Error in HopeThisWorks routine" & VBA.vbNewLine & _
    "Error: " & Err.Number & " - " & Err.Description
    Resume Exit_HopeThisWorks


    End Function
    </pre>


  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    okay, here it is:

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Hi Hans:

    This looks right. It looks like what I'm doing. I'm going to take a break and will test it tomorrow. Meanwhile, I'm checking to ensure the data is valid . . . you know how that goes. GIGO.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Math problem (Access 2002 XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Psst - Mike is the one who posted the demo and the code, not I !

  14. #14
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    I understand the (1+x1)*(1+x2)*(1+x3)...... part

    What does the (12/246) exponent mean? In my function I take the number of data points and divide by 12. I thought it was to annualize the months....or something like that.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Math problem (Access 2002 XP)

    Pffft. After I saw your spiral code on the other board, you can have credit for this one too.

    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

Page 1 of 2 12 LastLast

Posting Permissions

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