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 + 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 ' This function is a replacement for the Microsoft Excel Worksheet function NORMSINV. ' It uses the algorithm of Peter J. Acklam to compute the inverse normal cumulative ' distribution. Refer to http://home.online.no/~pjacklam/notes/invnorm/index.html for ' a description of the algorithm. ' Adapted to VB by Christian d'Heureuse, http://www.source-code.biz. Public Function NormSInv(ByVal p As Double) As Double    Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969    Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924    Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887    Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03    Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373    Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03    Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742    Const p_low = 0.02425, p_high = 1 - p_low    Dim q As Double, r As Double    If p < 0 Or p > 1 Then       Err.Raise vbObjectError, , "NormSInv: Argument out of range."     ElseIf p < p_low Then       q = Sqr(-2 * Log(p))       NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)     ElseIf p <= p_high Then       q = p - 0.5: r = q * q       NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _          (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)     Else       q = Sqr(-2 * Log(1 - p))       NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)       End If    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
3 Replies

 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 ' This function is a replacement for the Microsoft Excel Worksheet function NORMSINV. ' It uses the algorithm of Peter J. Acklam to compute the inverse normal cumulative ' distribution. Refer to http://home.online.no/~pjacklam/notes/invnorm/index.html for ' a description of the algorithm. ' Adapted to VB by Christian d'Heureuse, http://www.source-code.biz. Public Function NormSInv(ByVal p As Double) As Double    Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969    Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924    Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887    Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03    Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373    Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03    Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742    Const p_low = 0.02425, p_high = 1 - p_low    Dim q As Double, r As Double    If p < 0 Or p > 1 Then       Err.Raise vbObjectError, , "NormSInv: Argument out of range."     ElseIf p < p_low Then       q = Sqr(-2 * Log(p))       NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)     ElseIf p <= p_high Then       q = p - 0.5: r = q * q       NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _          (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)     Else       q = Sqr(-2 * Log(1 - p))       NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _          ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)       End If    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: ASSUMPTIONS Table Name: Table1 Field Name: ID Field Name: p_value 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 SELECT Table1.ID, Table1.p_value, NormSInv([p_value]) AS Inv_Norm_Cum FROM Table1; 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. 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 Public Function NormSInv(ByVal p As Double) As Double Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969 Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924 Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887 Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03 Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373 Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03 Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742 Const p_low = 0.02425, p_high = 1 - p_low Dim q As Double, r As Double   If p < 0 Or p > 1 Then   NormSInv = 0 ElseIf p < p_low Then   q = Sqr(-2 * Log(p))   NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _              ((((d1 * q + d2) * q + d3) * q + d4) * q + 1) ElseIf p <= p_high Then   q = p - 0.5: r = q * q   NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _              (((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1) Else   q = Sqr(-2 * Log(1 - p))   NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _               ((((d1 * q + d2) * q + d3) * q + d4) * q + 1) End If End Function SAMPLE OUTPUT: Expand|Select|Wrap|Line Numbers ID    p_value    Inv_Norm_Cum 1    0.2    -0.841621232726609 2    0.3    -0.524400513279294 3    0.4    -0.253347102859999 4    0.45    -0.125661346876103 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 Sigma: (0.8406+Sqr(29.37-2.221*(Log([PPM])))) Thanks for your help, though. JJ Jul 20 '07 #3

 Expert 5K+ P: 8,615 I ended up using the following to get the Sigma Rating: Expand|Select|Wrap|Line Numbers 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. 