locked
Equivalent of norm.inv function in excel to SSRS RRS feed

  • 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 Mike Yin 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.

    Below link will help you in writing that:

    http://answers.microsoft.com/en-us/office/forum/office_2007-excel/vba-code-instead-of-normsinvrand/219e3d66-231a-413f-a0ff-f0bd60dfec26

    Please let me know if you are facing any issue in writing the function

    Thanks & regards,

    Mark as answered if my post solved your problem


    Milan Das

    • Proposed as answer by Mike Yin Tuesday, September 24, 2013 1:19 PM
    Friday, September 20, 2013 8:57 PM