A SQL Script to Emulate Excel's NORMSINV
2007-10-02 (first published: 2002-06-20)
15,443 reads
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