The Excel "NORMSINV" Function in Visual Basic
The MS-Excel worksheet function NORMSINV computes the "inverse standard normal cumulative distribution". This function is not available in MS-Access (VBA) or VB6. When Office 2000 is installed, the undocumented function MSOWCFLib.OCFunc.NormSInv (in MSOWCF.DLL) can be used. But with Office 2003 this library is no longer installed.
The VB function below uses Peter J. Acklam's algorithm to compute the same values as the NORMSINV function of Excel.
' 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
Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
License: Free / LGPL
Index