Technical Article

NORMSINV

,

A SQL Script to Emulate Excel's NORMSINV

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  FUNCTION normsinv (@P float)

RETURNS float

as
begin
declare @a1 float
declare @a2 float
declare @a3 float
declare @a4 float
declare @a5 float
declare @a6 float

declare @b1 float
declare @b2 float
declare @b3 float
declare @b4 float
declare @b5 float

declare @c1 float
declare @c2 float
declare @c3 float
declare @c4 float
declare @c5 float
declare @c6 float

declare @d1 float
declare @d2 float
declare @d3 float
declare @d4 float

declare @plow float
declare @phigh float

declare @q float
declare @r float
declare @result float

Set @a1 = -39.6968302866538
Set @a2 = 220.946098424521
Set @a3 = -275.928510446969
Set @a4 = 138.357751867269
Set @a5 = -30.6647980661472
Set @a6 = 2.50662827745924

Set @b1 = -54.4760987982241
Set @b2 = 161.585836858041
Set @b3 = -155.698979859887
Set @b4 = 66.8013118877197
Set @b5 = -13.2806815528857

Set @c1 = -7.78489400243029E-03
Set @c2 = -0.322396458041136
Set @c3 = -2.40075827716184
Set @c4 = -2.54973253934373
Set @c5 = 4.37466414146497
Set @c6 = 2.93816398269878

Set @d1 = 7.78469570904146E-03
Set @d2 = 0.32246712907004
Set @d3 = 2.445134137143
Set @d4 = 3.75440866190742

set @plow=.02425
set @phigh=1-@plow

if (@p<@plow)
begin
set @q = Sqrt(-2 * Log(@p))
set @result=(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)
end
else
begin
if (@p<@phigh)
begin
set @q =@p - 0.5
set @r = @q * @q
set @result= (((((@a1 * @r + @a2) * @r + @a3) * @r + @a4) * @r + @a5) * @r + @a6) * @q / (((((@b1 * @r + @b2) * @r + @b3) * @r + @b4) * @r + @b5) * @r + 1)
end
else
begin
set @q = Sqrt(-2 * Log(1 - @p))
set @result= -(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)
end
end

return @result
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

5 (4)

Share

Share

Rate

5 (4)