NORMSINV Function

[alphaomega3]

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

' 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!

[ADezii]

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

' 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:

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:
      

      SELECT Table1.ID, Table1.p_value, NormSInv([p_value]) AS Inv_Norm_Cum
      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.
   

   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:

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

[alphaomega3]

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

Sigma: (0.8406+Sqr(29.37-2.221*(Log([PPM]))))

Thanks for your help, though.

JJ

[ADezii]

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

Sigma: (0.8406+Sqr(29.37-2.221*(Log([PPM]))))

Thanks for your help, though.

JJ

You're quite welcome.