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;