Calculate a Sigma Quality Level

[Methotika]

The function "normsinv" in Excel is used in an equation to calculate the
sigma quality level (sql) of a process. The equation is
"=NORMSINV(1-((B1)/(B2)))+1.5". I have not found a similar function in
access. Is there anyone who has developed an access function that will
operate in the same capacity as "normsinv" or know of such a function in
access or visual basic? I am operating in WIndows XP, Office XP, Access
2000, 2002 ... Thanks

 

[Michel Walsh]

Hi,


None built in that I know. Google seems to supply some mathematical
approximation for that function. You can also add Excel reference to your
project, and properly reference to that function in your VBA code (which is
easier, but *may* lead to a longer time in loading your application).



Hoping it may help,
Vanderghast, Access MVP

 

[Tim Ferguson]

The function "normsinv" in Excel is used in an equation to calculate
the sigma quality level (sql) of a process. The equation is
"=NORMSINV(1-((B1)/(B2)))+1.5". I have not found a similar function
in access. Is there anyone who has developed an access function that
will operate in the same capacity as "normsinv" or know of such a
function in access or visual basic? I am operating in WIndows XP,
Office XP, Access 2000, 2002 ... Thanks

I can't find my VBA version of this, but it's easy enough to convert
from this VBS function. Check the data types, that's all

Public Function SNorm2(z)
'***********************************************************************
'* Cumulative Standard Normal Distribution *
'* (this function provides similar result to NORMSDIST( ) on Excel) *
'* Source: http://www.geocities.com/WallStreet/9245/vba6.htm *
'***********************************************************************

Const c1 = 2.506628
Const c2 = 0.3193815
Const c3 = -0.3565638
Const c4 = 1.7814779
Const c5 = -1.821256
Const c6 = 1.3302744

Dim w, x , y

If z > 0 Or z = 0 Then
w = 1
Else
w = -1
End If

y = 1 / (1 + 0.231649 * w * z)

x = c6
x = y * x + c5
x = y * x + c4
x = y * x + c3
x = y * x + c2
SNorm2 = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * y * x)

End Function



Hope that helps


Tim F