By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

NORMSINV Function

P: 78
I need to be able to calculate the Six Sigma rating from the PPM. I can use NORMSINV function in Excel but it doesnt seem to work in Access.

=-NORMSINV(PPM/1000000)+1.5


Expand|Select|Wrap|Line Numbers
  1. ' This function is a replacement for the Microsoft Excel Worksheet function NORMSINV.
  2. ' It uses the algorithm of Peter J. Acklam to compute the inverse normal cumulative
  3. ' distribution. Refer to http://home.online.no/~pjacklam/notes/invnorm/index.html for
  4. ' a description of the algorithm.
  5. ' Adapted to VB by Christian d'Heureuse, http://www.source-code.biz.
  6. Public Function NormSInv(ByVal p As Double) As Double
  7.    Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969
  8.    Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924
  9.    Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
  10.    Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03
  11.    Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373
  12.    Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03
  13.    Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742
  14.    Const p_low = 0.02425, p_high = 1 - p_low
  15.    Dim q As Double, r As Double
  16.    If p < 0 Or p > 1 Then
  17.       Err.Raise vbObjectError, , "NormSInv: Argument out of range."
  18.     ElseIf p < p_low Then
  19.       q = Sqr(-2 * Log(p))
  20.       NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
  21.          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
  22.     ElseIf p <= p_high Then
  23.       q = p - 0.5: r = q * q
  24.       NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _
  25.          (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
  26.     Else
  27.       q = Sqr(-2 * Log(1 - p))
  28.       NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
  29.          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
  30.       End If
  31.    End Function
Is code that I found at the site listed in the comments of the code. However I can't seem to call this in the query where PPM is calculated. Help!
Jul 19 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,615
I need to be able to calculate the Six Sigma rating from the PPM. I can use NORMSINV function in Excel but it doesnt seem to work in Access.

=-NORMSINV(PPM/1000000)+1.5


Expand|Select|Wrap|Line Numbers
  1. ' This function is a replacement for the Microsoft Excel Worksheet function NORMSINV.
  2. ' It uses the algorithm of Peter J. Acklam to compute the inverse normal cumulative
  3. ' distribution. Refer to http://home.online.no/~pjacklam/notes/invnorm/index.html for
  4. ' a description of the algorithm.
  5. ' Adapted to VB by Christian d'Heureuse, http://www.source-code.biz.
  6. Public Function NormSInv(ByVal p As Double) As Double
  7.    Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969
  8.    Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924
  9.    Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
  10.    Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03
  11.    Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373
  12.    Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03
  13.    Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742
  14.    Const p_low = 0.02425, p_high = 1 - p_low
  15.    Dim q As Double, r As Double
  16.    If p < 0 Or p > 1 Then
  17.       Err.Raise vbObjectError, , "NormSInv: Argument out of range."
  18.     ElseIf p < p_low Then
  19.       q = Sqr(-2 * Log(p))
  20.       NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
  21.          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
  22.     ElseIf p <= p_high Then
  23.       q = p - 0.5: r = q * q
  24.       NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _
  25.          (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
  26.     Else
  27.       q = Sqr(-2 * Log(1 - p))
  28.       NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
  29.          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
  30.       End If
  31.    End Function
Is code that I found at the site listed in the comments of the code. However I can't seem to call this in the query where PPM is calculated. Help!
The following should point you in the right direction:
  1. ASSUMPTIONS
    1. Table Name: Table1
    2. Field Name: ID
    3. Field Name: p_value
    4. Field Name: Inv_Norm_Cum (this is a Calculated Field which passes the value in the [p_value] as an Argument to the NormSInv Function where it is analyzed and a Return Value generated. The SQL is listed below:
      Expand|Select|Wrap|Line Numbers
      1. SELECT Table1.ID, Table1.p_value, NormSInv([p_value]) AS Inv_Norm_Cum
      2. FROM Table1;
  2. The problem lies with a value of p that is outside of the permissible range, namely: < 0 or > 1. With values outside of the range, I simlpy have the Function return a 0, basically to indicate an Error State. It cannot return a Null Value since the return type is defined as Double.
  3. The modified line of code is listed below (Line #13) - follow this outline and all should be fine. Let me know how you make out.
    Expand|Select|Wrap|Line Numbers
    1. Public Function NormSInv(ByVal p As Double) As Double
    2. Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969
    3. Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924
    4. Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
    5. Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03
    6. Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373
    7. Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03
    8. Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742
    9. Const p_low = 0.02425, p_high = 1 - p_low
    10. Dim q As Double, r As Double
    11.  
    12. If p < 0 Or p > 1 Then
    13.   NormSInv = 0
    14. ElseIf p < p_low Then
    15.   q = Sqr(-2 * Log(p))
    16.   NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
    17.              ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
    18. ElseIf p <= p_high Then
    19.   q = p - 0.5: r = q * q
    20.   NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _
    21.              (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
    22. Else
    23.   q = Sqr(-2 * Log(1 - p))
    24.   NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
    25.               ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
    26. End If
    27. End Function
SAMPLE OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. ID    p_value    Inv_Norm_Cum
  2. 1    0.2    -0.841621232726609
  3. 2    0.3    -0.524400513279294
  4. 3    0.4    -0.253347102859999
  5. 4    0.45    -0.125661346876103
  6. 5    7         0
Jul 20 '07 #2

P: 78
I ended up using the following to get the Sigma Rating:

Expand|Select|Wrap|Line Numbers
  1. Sigma: (0.8406+Sqr(29.37-2.221*(Log([PPM]))))
Thanks for your help, though.

JJ
Jul 20 '07 #3

ADezii
Expert 5K+
P: 8,615
I ended up using the following to get the Sigma Rating:

Expand|Select|Wrap|Line Numbers
  1. Sigma: (0.8406+Sqr(29.37-2.221*(Log([PPM]))))
Thanks for your help, though.

JJ
You're quite welcome.
Jul 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.