Math Functions

excel-functions.js

    //
    /**
     * Returns the inverse of the standard normal cumulative distribution. 
     * The distribution has a mean of zero and a standard deviation of one.
     * Based on: https://github.com/liorzoue/js-normal-inverse
     * @param Number
     */
    function normsInv(x){
        if(isNaN(x)){
            throw new TypeError('normsInv requires a parameter of type Number');
        }
        var a =
        [
            -3.969683028665376e+01,
             2.209460984245205e+02,
            -2.759285104469687e+02,
             1.383577518672690e+02,
            -3.066479806614716e+01,
             2.506628277459239e+00
        ];

        var b =
        [
            -5.447609879822406e+01,
             1.615858368580409e+02,
            -1.556989798598866e+02,
             6.680131188771972e+01,
            -1.328068155288572e+01
        ];

        var c =
        [
            -7.784894002430293e-03,
            -3.223964580411365e-01,
            -2.400758277161838e+00,
            -2.549732539343734e+00,
             4.374664141464968e+00,
             2.938163982698783e+00
        ];

        var d =
        [
            7.784695709041462e-03,
            3.224671290700398e-01,
            2.445134137142996e+00,
            3.754408661907416e+00
        ];

        var LOW = 0.02425;
        var HIGH = 0.97575;


        return function (p) {
            var q, r;

            // errno = 0;

            if (p < 0 || p > 1)
            {
                // errno = EDOM;
                return 0.0;
            }
            else if (p == 0)
            {
                // errno = ERANGE;
                return Number.NEGATIVE_INFINITY; /* minus "infinity" */;
            }
            else if (p == 1)
            {
                // errno = ERANGE;
                return Number.POSITIVE_INFINITY; /* "infinity" */;
            }
            else if (p < LOW)
            {
                /* Rational approximation for lower region */
                q = Math.sqrt(-2*Math.log(p));
                return (((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) /
                    ((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1);
            }
            else if (p > HIGH)
            {
                /* Rational approximation for upper region */
                q  = Math.sqrt(-2*Math.log(1-p));
                return -(((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) /
                    ((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1);
            }
            else
            {
                /* Rational approximation for central region */
                    q = p - 0.5;
                    r = q*q;
                return (((((a[0]*r+a[1])*r+a[2])*r+a[3])*r+a[4])*r+a[5])*q /
                    (((((b[0]*r+b[1])*r+b[2])*r+b[3])*r+b[4])*r+1);
            }
        }(x);
    }

    /**
     * Excel Round implementation: The second parameter specifies to the ROUND function how many digits to use on the approximation.
     *    - 0 would approximate mathematically to the closest integer.
     *    - A positive number (n) would approximate using the most n significant digits - ie. ROUND(1.23456, 3) = 1,235
     *    - A negative number (-n) would approximate the first significant integer digits, then add "0" as padding to the rest - ie. ROUND(1.23456, -1) = 0, ROUND(12.3456, -1) = 10, ROUND(123456.7, -4) = 123500
     * source: http://stackoverflow.com/questions/27921342/convert-specific-excel-forumla-to-javascript
     * @param val
     * @param num
     * @returns {number}
     */
    function ROUND(val, num) {
      var coef = Math.pow(10, num);
      return (Math.round(val * coef))/coef
}