## 12 September 2012

### Part 3: T-SQL Implementation of NORMDIST / NORM.S.DIST

This is Part 3 of a set of posts. The other posts are:
Part 1: Introduction
Part 2: Probability Density Function Implementation
Part 3: Cumulative Distribution Function Implementation 1
Part 4: Cumulative Distribution Function Implementation 2
Part 5: Cumulative Distribution Function Implementation 3
Part 6: References and Notes

# Cumulative Distribution Function Implementation 1

The cumulative distribution function has no closed form expression and can only be approximated (see Part 1.)
The following implementation uses polynomial approximation 26.2.18 from Abramowitz & Stegun (1964) (see Part 6) for full references.

-- =============================================
-- Author:    Eli Algranti
-- Description:  Standard Normal Distribution
--        Cummulative Distribution Function
--        using polinomial approximation 26.2.18
--        from Abramowitz & Stegun (1964)
-- Copyright:  Eli Algranti (c) 2012
--
-- This code is licensed under the Microsoft Public
-- =============================================
CREATE FUNCTION [dbo].[Stdnormaldistributioncdf_1] (@x FLOAT)
returns FLOAT
AS
BEGIN
DECLARE @Z FLOAT = Abs(@x)

IF ( @Z >= 15 ) -- value is too large no need to compute
BEGIN
IF @x > 0
RETURN 1;

RETURN 0;
END

-- Compute the Standard Normal Cummulative Distribution using
-- polinomial approximation 26.2.18 from Abramowitz & Stegun (1964)
DECLARE @c1 FLOAT = 0.196854;
DECLARE @c2 FLOAT = 0.115194;
DECLARE @c3 FLOAT = 0.000344;
DECLARE @c4 FLOAT = 0.019527;
-- For efficiency compute sequence of powers of @Z (instead of calling POWER(@Z,2), POWER(@Z,3), etc.)
DECLARE @Z2 FLOAT = @Z * @Z;
DECLARE @Z3 FLOAT = @Z2 * @Z;
DECLARE @cd FLOAT = 1.0 - 0.5 * Power(1 + @c1 * @Z + @c2 * @Z2 + @c3 * @Z3
+
@c4 * @Z3 * @Z, -4)

IF @x > 0
RETURN @cd;

RETURN 1.0 - @cd;
END

### Verification Using Excel 2010 as Reference

Maximum difference 0.000232983371206426 at value -1.82000000000017 ### Verification Using Excel 2007 as Reference

Maximum difference 0.000232983371206363 at value -1.82000000000017 ### Discussion

This approximation provides a precision of three decimal places, which is enough for many applications.
The main advantage of this implementation is its simplicity, requiring just a handful of arithmetic operations.

#### 1 comment:

1. Hi, How does this work when the @Z value is negative?