Therefore after decoding Microsoft's miniature equation in their help file,
I have created the below function.
I have created the below function.
create or replace FUNCTION "NORMDIST"(
x_VAL IN number,
x_Mean IN number,
x_STDEV IN number
)
RETURN number
IS
NDIST number;
BEGIN
NDIST := round((1/(sqrt(asin(1)*4)*X_STDEV))*exp(-((power((X_VAL-X_MEAN),2))/(2*power(X_STDEV,2)))),14);
RETURN (NDIST);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
The function should produce the same outcome as "=NORMDIST(Val, Mean, Stdev, False)" in Excel. I don't have a degree in maths, but after testing the code it works and displays the same results. Hope it helps.
CREATE OR REPLACE FUNCTION GET_NORMSDIST (x NUMBER)
ReplyDeleteRETURN NUMBER
IS
result NUMBER;
L NUMBER;
K NUMBER;
dCND NUMBER;
pi NUMBER;
a1 NUMBER;
a2 NUMBER;
a3 NUMBER;
a4 NUMBER;
a5 NUMBER;
BEGIN
L := 0.0;
K := 0.0;
dCND := 0.0;
a1 := 0.31938153;
a2 := -0.356563782;
a3 := 1.781477937;
a4 := -1.821255978;
a5 := 1.330274429;
pi := 3.1415926535897932384626433832795;
L := ABS (x);
IF L >= 30
THEN
BEGIN
IF SIGN (x) = 1
THEN
result := 1;
ELSE
result := 0;
END IF;
END;
ELSE
BEGIN
-- perform calculation
K := 1.0 / (1.0 + 0.2316419 * L);
dCND :=
1.0
- 1.0
/ SQRT (2 * pi)
* EXP (-L * L / 2.0)
* ( a1 * K
+ a2 * K * K
+ a3 * POWER (K, 3.0)
+ a4 * POWER (K, 4.0)
+ a5 * POWER (K, 5.0));
IF (x < 0)
THEN
result := 1.0 - dCND;
ELSE
result := dCND;
END IF;
END;
END IF;
RETURN result;
END;