Is there an equivalent of Excel's NORMDIST function in SQL Server 2005?

  • L Cerniglia

    Mr or Mrs. 500

    Points: 579

    Is there an equivalent of Excel's NORMDIST function in SQL Server 2005?

  • Sean Lange

    SSC Guru

    Points: 286515

    Nothing right out of the box that i know of. You will probably have to write your own. (or borrow somebody else's)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • L Cerniglia

    Mr or Mrs. 500

    Points: 579

    If it helps anyone, this is how my function looks like. Thanks again to all who helped me.

    CREATE FUNCTION [dbo].[udf_NORMDIST](@value FLOAT,

    @mean FLOAT,

    @sigma FLOAT,

    @cummulative BIT)

    RETURNS NUMERIC(28,8)

    AS

    /****************************************************************************************

    NAME: udf_NORMDIST

    WRITTEN BY: Tim Pickering

    http://www.eggheadcafe.com/software/aspnet/31021839/normdistx-mean-standarddevtrue-in-sql-2005.aspx

    DATE: 2010/07/13

    PURPOSE: Mimics Excel's Function NORMDIST

    Usage: SELECT dbo.udf_NORMDIST(.48321740,0,1,0)

    OUTPUT: 0.35498205

    REVISION HISTORY

    Date Developer Details

    2010/08/11 LC Posted Function

    *****************************************************************************************/

    BEGIN

    DECLARE @x FLOAT

    DECLARE @z FLOAT

    DECLARE @t FLOAT

    DECLARE @ans FLOAT

    DECLARE @returnvalue FLOAT

    SELECT @x = (@value-@mean)/@sigma

    IF (@cummulative = 1)

    BEGIN

    SELECT @z = abs(@x)/sqrt(2.0)

    SELECT @t = 1.0/(1.0+0.5*@z)

    SELECT @ans = @t*exp(-@z*@z-1.26551223+@t*(1.00002368+@t*(0.37409196+@t*(0.09678418+@t*(-0.18628806+@t*(0.27886807+@t*(-1.13520398+@t*(1.48851587+@t*(-0.82215223+@t*0.17087277)))))))))/2.0

    IF (@x <= 0)

    SELECT @returnvalue = @ans

    ELSE

    SELECT @returnvalue = 1-@ans

    END

    ELSE

    BEGIN

    SELECT @returnvalue = exp(-@x*@x/2.0)/sqrt(2.0*3.14159265358979)

    END

    RETURN CAST(@returnvalue AS NUMERIC(28,8))

    END

  • idea

    SSC Journeyman

    Points: 79

    Out of the box there is no equivalent in SQL Server (up to 2012) for NORMDIST.

    NORMDIST computes the Normal Distribution PDF (Probability Density Function) when the cumulative parameter is false and the CDF (Cumulative Distribution Function) otherwise.

    The PDF can be computed from the definition and is pretty straight forward, the CDF however has no close form and can only be approximated.

    I've developed a function for the PDF and a set of functions using different approximations for the CDF.

    You can read my analysis in:

    http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html

    and either copy paste the ones you want from the posts or get them from the project page for the blog posts at:

    https://tsqlnormdist.codeplex.com/

  • chris.palmer.nz

    SSC Journeyman

    Points: 86

    I have been able to use the udf_NORMDIST code to stand in for Excel NORM.DIST (NORMDIST). Thank-you very much for posting it!

    The other reference from idea/Forum Newbie points to code for NORM.S.DIST (NORMSDIST in older Excel), it seems to be incorrectly labelled as an equivalent for NORMDIST.

    However the value returned when @cummulative is set to false (0) seems to be incorrect with some larger numbers, at least it doesn't line up with Excel.

    e.g. I used SELECT dbo.udf_NORMDIST(358, 352.791176470588, 234.354144866830, 0)

    Excel gives me 0.001701885, but this function gave me 0.398843752

    So I created a replacement for the line that returns a value when not using cumulative

    The formula for this change comes from http://stattrek.com/online-calculator/normal.aspx

    Normal equation:

    The value of the random variable Y is:

    Y = { 1/[ s * sqrt(2p) ] } * e-(x - µ)2/2s2

    where X is a normal random variable,

    µ is the mean,

    s is the standard deviation,

    p is approximately 3.14159,

    and e is approximately 2.71828.

    Code change:

    -- Incorrect for larger numbers?

    --SELECT @returnvalue = exp(-@x*@x/2.0)/sqrt(2.0*3.14159265358979)

    -- Correct for any numbers...

    SELECT @returnvalue = (1/(@sigma * sqrt(2.0*3.14159265358979))) * exp(-((@value - @mean)*(@value - @mean))/(2*(@sigma*@sigma)))

    Also, the precision is not quite right, it needs to be 29,9, not 28,8

    Here is a replacement for the function:

    CREATE FUNCTION [dbo].[udf_NORMDIST](

    @value FLOAT,

    @mean FLOAT,

    @sigma FLOAT,

    @cummulative BIT )

    RETURNS NUMERIC( 29,9 )

    AS

    /****************************************************************************************

    NAME: udf_NORMDIST

    WRITTEN BY: Tim Pickering

    http://www.eggheadcafe.com/software/aspnet/31021839/normdistx-mean-standarddevtrue-in-sql-2005.aspx

    DATE: 2010/07/13

    PURPOSE: Mimics Excel's Function NORMDIST

    Usage: SELECT dbo.udf_NORMDIST(.48321740,0,1,0)

    OUTPUT: 0.35498205

    REVISION HISTORY

    Date Developer Details

    2010/08/11 LC Posted Function

    *****************************************************************************************/

    BEGIN

    DECLARE @x FLOAT;

    DECLARE @z FLOAT;

    DECLARE @t FLOAT;

    DECLARE @ans FLOAT;

    DECLARE @returnvalue FLOAT;

    SELECT @x = (@value - @mean) / @sigma;

    IF(@cummulative = 1)

    BEGIN

    SELECT @z = ABS( @x ) / SQRT( 2.0 );

    SELECT @t = 1.0 / (1.0 + 0.5 * @z);

    SELECT @ans = @t*exp(-@z*@z-1.26551223+@t*(1.00002368+@t*(0.37409196+@t*(0.09678418+@t*(-0.18628806+@t*(0.27886807+@t*(-1.13520398+@t*(1.48851587+@t*(-0.82215223+@t*0.17087277)))))))))/2.0;

    IF(@x <= 0)

    SELECT @returnvalue = @ans;

    ELSE

    SELECT @returnvalue = 1 - @ans;

    END;

    ELSE

    BEGIN

    SELECT @returnvalue = (1/(@sigma * SQRT( 2.0 * 3.14159265358979 ))) * EXP(-((@value-@mean)*(@value-@mean)) / (2*(@sigma*@sigma)));

    END;

    RETURN CAST( @returnvalue AS NUMERIC( 29,9 ));

    END;

  • Dwain Camps

    SSC Guru

    Points: 86883

    Nothing out of the box as stated, but there is this:

    Excel in T-SQL Part 2 – The Normal Distribution (NORM.DIST) Density Functions[/url]

    There are also some links at the end to approximation formulas for the cumulative distribution function. The probability density function is pretty straightforward.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • imsmart

    Newbie

    Points: 1

    use this for POSTGRESQL:

    -- Start

    CREATE OR REPLACE FUNCTION stock.udf_NORMDIST(

    value1 FLOAT,

    mean float,

    sigma float,

    cummulative float )

    RETURNS NUMERIC( 29,9 )

    AS $total$

    declare total integer;

    DECLARE x FLOAT;

    DECLARE z FLOAT;

    DECLARE t FLOAT;

    DECLARE ans FLOAT;

    DECLARE returnvalue FLOAT;

    BEGIN

    select (value1 - mean) / sigma into x;

    IF(cummulative = 1) then

    select ABS( x ) / SQRT( 2.0 ) into z;

    select 1.0 / (1.0 + 0.5 * z) into t;

    select (t*exp(-z*z-1.26551223+t*(1.00002368+t*(0.37409196+t*(0.09678418+t*(-0.18628806+t*(0.27886807+t*(-1.13520398+t*(1.48851587+t*(-0.82215223+t*0.17087277)))))))))/2.0) into ans;

    IF(x <= 0) then

    SELECT ans into returnvalue;

    ELSE

    SELECT 1 - ans into returnvalue;

    END IF;

    ELSE

    SELECT (1/(sigma * SQRT( 2.0 * 3.14159265358979 ))) * EXP(-((value1-mean)*(value1-mean)) / (2*(sigma*sigma))) INTO returnvalue;

    END IF;

    --RETURN x;

    RETURN CAST( returnvalue * 100 AS NUMERIC( 29,2 ));

    END; $total$

    LANGUAGE PLPGSQL;

    -- END

    example usage: SELECT stock.udf_NORMDIST(-2.55,0,1,1)

  • Sean Lange

    SSC Guru

    Points: 286515

    imsmart wrote:

    use this for POSTGRESQL:

    -- Start

    This really isn't helpful. This code is POSTGRE but this is a sql server forum.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply