# Equivalent of norm.inv function in excel to SSRS

• ### Question

• Hi,

Urgent help required :

Please let me know the equivalent function of NORM.INV() in excel with SSRS. Actually I have a excel report which has a column with norm.inv function used as =NORM.INV(0.9,B2,F2)

I want to get a SSRS Report but I do not have a clue how to convert the function NORM.INV()

Thanks

Ajit

Friday, September 20, 2013 10:24 AM

### All replies

• NORMINV(p, mu, sigma) = mu + sigma*NORMSINV(p)

Use the following function to arrive on NORMSINV in SQL. And then return the NORMINV value from SQL itself, and use that in your datasource in SSRS. Hope this helps.

CREATE  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

• Proposed as answer by Tuesday, September 24, 2013 1:19 PM
Friday, September 20, 2013 4:48 PM
• Hi Ajit,

For achieving this you need to write a custom code in V.B.