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;