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