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)