/* StatFunctionImpl.java

	Purpose:
		
	Description:
		
	History:
		Oct 14, 2010 4:23:06 PM, Created by ashish

Copyright (C) 2010 Potix Corporation. All Rights Reserved.

*/
package org.zkoss.zssex.formula.fn;

import java.util.List;

import org.apache.commons.math.distribution.BinomialDistribution;
import org.apache.commons.math.distribution.ChiSquaredDistribution;
import org.apache.commons.math.distribution.ExponentialDistribution;
import org.apache.commons.math.MathException;
import org.apache.commons.math.distribution.BetaDistributionImpl;
import org.apache.commons.math.distribution.BinomialDistributionImpl;
import org.apache.commons.math.distribution.ChiSquaredDistributionImpl;
import org.apache.commons.math.distribution.ExponentialDistributionImpl;
import org.apache.commons.math.distribution.FDistribution;
import org.apache.commons.math.distribution.FDistributionImpl;
import org.apache.commons.math.distribution.GammaDistribution;
import org.apache.commons.math.distribution.GammaDistributionImpl;
import org.apache.commons.math.distribution.HypergeometricDistribution;
import org.apache.commons.math.distribution.HypergeometricDistributionImpl;
import org.apache.commons.math.distribution.NormalDistribution;
import org.apache.commons.math.distribution.NormalDistributionImpl;
import org.apache.commons.math.distribution.PascalDistributionImpl;
import org.apache.commons.math.distribution.PoissonDistribution;
import org.apache.commons.math.distribution.PoissonDistributionImpl;
import org.apache.commons.math.distribution.TDistribution;
import org.apache.commons.math.distribution.TDistributionImpl;
import org.apache.commons.math.distribution.WeibullDistribution;
import org.apache.commons.math.distribution.WeibullDistributionImpl;
import org.apache.commons.math.special.Gamma;
import org.apache.commons.math.stat.correlation.PearsonsCorrelation;
import org.apache.commons.math.stat.descriptive.DescriptiveStatistics;
import org.apache.commons.math.util.FastMath;
import org.zkoss.poi.ss.formula.eval.ErrorEval;
import org.zkoss.poi.ss.formula.eval.EvaluationException;
import org.zkoss.poi.ss.formula.eval.OperandResolver;
import org.zkoss.poi.ss.formula.eval.ValueEval;
import org.zkoss.poi.ss.formula.AreaEvalHelper;
import org.zkoss.poi.ss.formula.functions.Function;
import org.zkoss.poi.ss.formula.functions.Mode;
import org.zkoss.poi.ss.formula.functions.NumericFunction;
import org.zkoss.poi.ss.formula.functions.NumericFunctionHelper;
import org.zkoss.poi.ss.formula.functions.Rank;
import org.zkoss.poi.ss.formula.functions.UtilFns;


/**
 * @author ashish, kuro
 *
 */
public class StatFunctionImpl {
	
	public static final Function AVERAGEA = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			final List ls = UtilFns.toList(args, srcRowIndex, srcColumnIndex);
			if (ls.isEmpty()) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			
			return NumericFunctionHelper.checkValue((UtilFns.getStats(UtilFns.toDoubleArray(ls)).getMean()));
		}
	};
	
	public static final Function BINOMDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double number = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			int trails = (int) NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double p_s = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
            ValueEval ve = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
            Boolean isCumulative = OperandResolver.coerceValueToBoolean(ve, false);
    		if(number>trails || number <0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
    		} else {
    			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
    			BinomialDistribution bd = new BinomialDistributionImpl(trails, p_s);
    			if(isCumulative)
					try {
						return NumericFunctionHelper.checkValue(bd.cumulativeProbability((int)number));
					} catch (MathException e) {
						throw new EvaluationException(ErrorEval.NUM_ERROR);
					}
				else
    				return NumericFunctionHelper.checkValue(bd.probability(number));
    			
    		}
		}
	};
	public static final Function CHIDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df= NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if(x<0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
				ChiSquaredDistribution chi = new ChiSquaredDistributionImpl(df);
				try {
					return NumericFunctionHelper.checkValue(1- chi.cumulativeProbability(x));
				} catch (MathException e) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
			}
		}
	};
	public static final Function CHIINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df= NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if(p<0 || p>1) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
				ChiSquaredDistribution chi = new ChiSquaredDistributionImpl(df);
				try {
					return NumericFunctionHelper.checkValue(chi.inverseCumulativeProbability((1-p)));
				} catch (MathException e) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
			}
		}
	};
	public static final Function EXPONDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double lambda = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double mean = 1 / lambda;
            ValueEval ve = OperandResolver.getSingleValue(args[2], srcRowIndex, srcColumnIndex);
            Boolean isCumulative = OperandResolver.coerceValueToBoolean(ve, false);
            // upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
    		ExponentialDistribution expDist = new ExponentialDistributionImpl(mean);
			try {
	    		if(isCumulative) {
					return NumericFunctionHelper.checkValue(expDist.cumulativeProbability(x));
	    		} else {
	    			return NumericFunctionHelper.checkValue(expDist.density(x)); //ZSS-1210
	    		}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
		}
	};
	public static final Function FDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double ddf = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			FDistribution fDist = new FDistributionImpl(df, ddf);
			double result;
			try {
				result = 1 - fDist.cumulativeProbability(x);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function FINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double ddf = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			FDistribution fDist = new FDistributionImpl(df, ddf);
			double result;
			try {
				result = fDist.inverseCumulativeProbability(1 - p);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function GAMMADIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
            ValueEval ve = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
            Boolean isCumulative = OperandResolver.coerceValueToBoolean(ve, false);
            // upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
            GammaDistribution gammaDist = new GammaDistributionImpl(alpha, beta);
			try {
	    		if(isCumulative) {
					return NumericFunctionHelper.checkValue(gammaDist.cumulativeProbability(x));
	    		} else {
	    			return NumericFunctionHelper.checkValue(gammaDist.density(x));
	    		}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
		}
	};
	public static final Function GAMMAINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			GammaDistribution gammaDist = new GammaDistributionImpl(alpha, beta);
			double result;
			try {
				result = gammaDist.inverseCumulativeProbability(p);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function GAMMALN = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			return NumericFunctionHelper.checkValue(Gamma.logGamma(x));
		}
	};
	public static final Function GEOMEAN = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			return NumericFunctionHelper.checkValue(UtilFns.getStats(UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex))).getGeometricMean());
		}
	};
	public static final Function HARMEAN = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			// reference: http://en.wikipedia.org/wiki/Harmonic_mean
			double result = 0;
			double[] values = UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex));
			for(double v : values) {
				result += (1 / v); // sum of Multiplicative inverse
			}
			if(result != 0) { // prevent for runtime error divide by 0
				result = values.length / result;
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function HYPGEOMDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			//EXCEL: HYPGEOMDIST(sample_s,number_sample,population_s,number_population)
			/*
				Sample_s   is the number of successes in the sample.
				
				Number_sample   is the size of the sample.
				
				Population_s   is the number of successes in the population.
				
				Number_population   is the population size.
			 */
			int s = (int) NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			int ns = (int) NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			int p = (int) NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			int np = (int) NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			boolean cumulative = args.length <= 4 ? false :
				NumericFunction.singleOperandEvaluateToBoolean(args[4], srcRowIndex, srcColumnIndex);
			if (s < 0 || s > ns || s > p || s < (ns - np + p) || ns <= 0 || ns > np || p <= 0 || p > np || np <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			HypergeometricDistribution hDist = new HypergeometricDistributionImpl(np, p, ns);
			//ZSS-1235
			double result;
			try {
				if (cumulative) {
					result = hDist.cumulativeProbability(s);
				} else {
					result = hDist.probability(s);
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function INTERCEPT = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			//TODO: Implement this function.
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		}
	};
	public static final Function KURT = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			return NumericFunctionHelper.checkValue(UtilFns.getStats(UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex))).getKurtosis());
		}
	};
	
	public static final Function NORMDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double sDev = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			//ZSS-1210
			boolean cumulative = NumericFunction.singleOperandEvaluateToBoolean(args[3], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			NormalDistribution normDist = new NormalDistributionImpl(mean,sDev);
			double result;
			try {
				//ZSS-1210
				if (cumulative) {
					result = normDist.cumulativeProbability(x);
				} else {
					result = normDist.density(x);
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	
	public static final Function POISSON = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
            ValueEval ve = OperandResolver.getSingleValue(args[2], srcRowIndex, srcColumnIndex);
            Boolean isCumulative = OperandResolver.coerceValueToBoolean(ve, false);
            // upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
            PoissonDistribution poiDist = new PoissonDistributionImpl(mean);
			try {
	    		if(isCumulative) {
					return NumericFunctionHelper.checkValue(poiDist.cumulativeProbability((int)x));
	    		} else {
	    			return NumericFunctionHelper.checkValue(poiDist.probability((int)x));
	    		}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
		}
	};
	public static final Function SKEW = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			return NumericFunctionHelper.checkValue(UtilFns.getStats(UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex))).getSkewness());
		}
	};
	public static final Function SLOPE = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			List l0 = AreaEvalHelper.toDoubleList(args[0], srcRowIndex, srcColumnIndex);
			List l1 = AreaEvalHelper.toDoubleList(args[1], srcRowIndex, srcColumnIndex);
			return NumericFunctionHelper.checkValue(
					UtilFns.getRegre(UtilFns.toDoubleArray(l1) ,UtilFns.toDoubleArray(l0)).getSlope());
		}
	};
	public static final Function STDEV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			return NumericFunctionHelper.checkValue(UtilFns.getStats(UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex))).getStandardDeviation());
		}
	};
	public static final Function TDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double degree_freedom = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			int tails = (int) NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			return tdist(x, degree_freedom, tails); //ZSS-1236
		}
	};
	//ZSS-1236
	public static final Function TDIST2T = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double degree_freedom = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			return tdist(x, degree_freedom, 2);
		}
	};
	//ZSS-1236
	public static final Function TDISTRT = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double degree_freedom = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			return tdist(x, degree_freedom, 1);
		}
	};
	//ZSS-1236
	private static final double tdist(double x, double degree_freedom, int tails) throws EvaluationException {
		// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
		TDistribution td = new TDistributionImpl(degree_freedom);
		double result;
		try {
			if(tails == 1) { // one tail.
				result = 1 - td.cumulativeProbability(x);
			} else if(tails == 2) { // two tails.
				result = (1 - td.cumulativeProbability(x)) * 2;
			} else { // invalidate tail value.
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
		} catch (MathException e) {
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		}
		return NumericFunctionHelper.checkValue(result);
	}
	public static final Function TINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double degree_freedom = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			TDistribution td = new TDistributionImpl(degree_freedom);
			double result;
			try {
				result = td.inverseCumulativeProbability(1-(p/2));
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	public static final Function VAR = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			return NumericFunctionHelper.checkValue(UtilFns.getStats(UtilFns.toDoubleArray(UtilFns.toList(args, srcRowIndex, srcColumnIndex))).getVariance());
		}
	};
	public static final Function WEIBULL = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			//ZSS-1235
			boolean cumulative = NumericFunction.singleOperandEvaluateToBoolean(args[3], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			WeibullDistribution wb = new WeibullDistributionImpl(alpha, beta);
			double result;
			try {
				//ZSS-1235
				if (cumulative) {
					result = wb.cumulativeProbability(x);
				} else {
					result = ((WeibullDistributionImpl)wb).density(x);
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};

	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/NORMINV-function-87981AB8-2DE0-4CB0-B1AA-E21D4CB879B8
	public static final Function NORMINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double sDev = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			NormalDistribution normDist = new NormalDistributionImpl(mean,sDev);
			double result;
			try {
				result = normDist.inverseCumulativeProbability(x);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// NORMSDIST(x) == NORMDIST(x, 0, 1, true); 
	public static final Function NORMSDIST = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			boolean cumulative = args.length <= 1 ? true : 
					NumericFunction.singleOperandEvaluateToBoolean(args[1], srcRowIndex, srcColumnIndex);
			NormalDistribution normDist = new NormalDistributionImpl();
			double result;
			try {
				if (cumulative) {
					result = normDist.cumulativeProbability(x);
				} else {
					result = normDist.density(x);
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/NORMSINV-function-8d1bce66-8e4d-4f3b-967c-30eed61f019d
	public static final Function NORMSINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			NormalDistribution normDist = new NormalDistributionImpl();
			double result;
			try {
				result = normDist.inverseCumulativeProbability(x);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/LOGNORMDIST-function-f8d194cb-9ee3-4034-8c75-1bdb3884100b
	// see https://en.wikipedia.org/wiki/Log-normal_distribution#Probability_density_function
	//LOGNORMDIST(x, mean, std_dev) == NORMSDIST((log(x) - mean) / std_dev);
	public static final Function LOGNORMDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double sDev = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			boolean cumulative = args.length <= 3 ? true : 
				NumericFunction.singleOperandEvaluateToBoolean(args[3], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			NormalDistribution normDist = new NormalDistributionImpl(mean,sDev);
			double result;
			try {
				final double logx = FastMath.log(x);
				//ZSS-1210
				if (cumulative) {
					result = normDist.cumulativeProbability(logx);
				} else {
					result = normDist.density(logx) / x;
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/LOGNORM-INV-function-FE79751A-F1F2-4AF8-A0A1-E151B2D4F600
	//LOGNORMINV(x, mean, std_dev) == exp(NORMSINV(x) * std_dev + mean);
	public static final Function LOGNORMINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double sDev = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			NormalDistribution normDist = new NormalDistributionImpl();
			double result;
			try {
				final double invx = normDist.inverseCumulativeProbability(x);
				result = FastMath.exp(invx * sDev + mean);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/BINOM-INV-function-80A0370C-ADA6-49B4-83E7-05A91BA77AC9
	public static final Function BINOMINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			int trial = (int) NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double p = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			if (trial < 0 || p <= 0 || p >= 1 || alpha <= 0 || alpha >= 1) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			BinomialDistribution binomDist = new BinomialDistributionImpl(trial, p);
			double result;
			try {
				int result0 = binomDist.inverseCumulativeProbability(alpha);
				final double prop = binomDist.cumulativeProbability(result0);
				result = (double) (prop == alpha ? result0 : result0 + 1);
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	//see https://support.office.com/en-US/article/BETADIST-function-49F1B9A9-A5DA-470F-8077-5F1730B5FD47
	public static final Function BETADIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double a = args.length <= 3 ? 0 :
				NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double b = args.length <= 4 ? 1 :
				NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			return betadist(x, alpha, beta, true, a, b);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	//see https://support.office.com/en-US/article/BETA-DIST-function-11188C9C-780A-42C7-BA43-9ECB5A878D31
	public static final Function BETA$DIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			boolean cumulative = NumericFunction.singleOperandEvaluateToBoolean(args[3], srcRowIndex, srcColumnIndex);
			double a = args.length <= 4 ? 0 :
				NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			double b = args.length <= 5 ? 1 :
				NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
			return betadist(x, alpha, beta, cumulative, a, b);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	private static final double betadist(double x, double alpha, double beta, 
			boolean cumulative, double a, double b) throws EvaluationException {
		if (alpha <= 0 || beta <= 0 || x < a || x > b || a == b) {
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		}
		// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
		BetaDistributionImpl betaDist = new BetaDistributionImpl(alpha, beta);
		double result;
		try {
			x = (x - a) / (b - a);
			if (cumulative) {
				result = betaDist.cumulativeProbability(x);
			} else {
				result = betaDist.density(x) / 2;
			}
		} catch (MathException e) {
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		}
		return NumericFunctionHelper.checkValue(result);
	}
	//ZSS-1210
	//@since 3.9.1
	//see https://support.office.com/en-US/article/BETAINV-function-8B914ADE-B902-43C1-AC9C-C05C54F10D6C
	public static final Function BETAINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double alpha = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double beta = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double a = args.length <= 3 ? 0 :
				NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double b = args.length <= 4 ? 1 :
				NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			if (alpha <= 0 || beta <= 0 || p <= 0 || p > 1 || a == b) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			BetaDistributionImpl betaDist = new BetaDistributionImpl(alpha, beta);
			double result;
			try {
				result = betaDist.inverseCumulativeProbability(p);
				result = result * (b - a) + a;
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	//see https://support.office.com/en-us/article/CHISQ-DIST-function-8486b05e-5c05-4942-a9ea-f6b341518732?ui=en-US&rs=en-US&ad=US
	public static final Function CHISQDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double x = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df= NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			boolean cumulative = NumericFunction.singleOperandEvaluateToBoolean(args[2], srcRowIndex, srcColumnIndex);
			if(x<0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
				ChiSquaredDistribution chi = new ChiSquaredDistributionImpl(df);
				try {
					if (cumulative) {
						return NumericFunctionHelper.checkValue(chi.cumulativeProbability(x));
					} else {
						return NumericFunctionHelper.checkValue(chi.density(x));
					}
				} catch (MathException e) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
			}
		}
	};
	//ZSS-1210
	//@since 3.9.1
	//see https://support.office.com/en-us/article/CHISQ-DIST-function-8486b05e-5c05-4942-a9ea-f6b341518732?ui=en-US&rs=en-US&ad=US
	public static final Function CHISQINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double df= NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if(p<0 || p > 1 || df < 1 || df > 10000000000.0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
				ChiSquaredDistribution chi = new ChiSquaredDistributionImpl(df);
				try {
					return NumericFunctionHelper.checkValue(chi.inverseCumulativeProbability(p));
				} catch (MathException e) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
			}
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// Don't know why, but there is no POISSION.INV in Excel. This is extra
	// function ZSS provided.
	public static final Function POISSONINV = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double p = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double mean = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
            // upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
            PoissonDistribution poiDist = new PoissonDistributionImpl(mean);
			try {
				return NumericFunctionHelper.checkValue(poiDist.inverseCumulativeProbability(p));
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-US/article/CORREL-function-995DCEF7-0C0A-4BED-A3FB-239D7B68CA92
	public static final Function CORREL = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			ValueEval[] arg0 = new ValueEval[] {args[0]};
			ValueEval[] arg1 = new ValueEval[] {args[1]};
			final List ls1 = UtilFns.toList(arg0, srcRowIndex, srcColumnIndex);
			if (ls1.isEmpty()) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			final List ls2 = UtilFns.toList(arg1, srcRowIndex, srcColumnIndex);
			if (ls2.isEmpty()) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			double[] xa = UtilFns.toDoubleArray(ls1);
			DescriptiveStatistics x = UtilFns.getStats(xa);
			double xstd = x.getStandardDeviation();
			int xn = xa.length;
			if (xstd == 0 || xn <= 1) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			double[] ya = UtilFns.toDoubleArray(ls2);
			DescriptiveStatistics y = UtilFns.getStats(ya);
			int yn = ya.length;
			double ystd = y.getStandardDeviation();
			if (ystd == 0 || yn <= 1) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			if (xn != yn) {
				throw new EvaluationException(ErrorEval.NA);
			}
			final double result = new PearsonsCorrelation().correlation(xa, ya);
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1210
	//@since 3.9.1
	// see https://support.office.com/en-us/article/NEGBINOM-DIST-function-c8239f89-c2d0-45bd-b6af-172e570f8599?ui=en-US&rs=en-US&ad=US
	// aka. PascalDistribution
	public static final Function NEGBINOMDIST = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double r = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double k = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double p = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			boolean cumulative = args.length <= 3 ? false :
				NumericFunction.singleOperandEvaluateToBoolean(args[3], srcRowIndex, srcColumnIndex);
			// upgrade to apache common math 2.2, no factory interface in 2.2, use Impl class directly. 
			PascalDistributionImpl normDist = new PascalDistributionImpl((int)k, p);
			double result;
			try {
				if (cumulative) {
					result = normDist.cumulativeProbability(r);
				} else {
					result = normDist.probability((int)r);
				}
			} catch (MathException e) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return NumericFunctionHelper.checkValue(result);
		}
	};
	//ZSS-1235
	//@since 3.9.1
	// see https://support.office.com/en-us/article/MODE-SNGL-function-f1267c16-66c6-4386-959f-8fba5f8bb7f8?ui=en-US&rs=en-US&ad=US
	public static final Function MODESNGL = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			final List ls = UtilFns.toList(args, srcRowIndex, srcColumnIndex);
			double[] vals = UtilFns.toDoubleArray(ls);
			return NumericFunctionHelper.checkValue(Mode.evaluate(vals));
		}
	};
	//ZSS-1235
	//@since 3.9.1
	// see https://support.office.com/en-us/article/MODE-SNGL-function-f1267c16-66c6-4386-959f-8fba5f8bb7f8?ui=en-US&rs=en-US&ad=US
	public static final Function RANKEQ = new Rank();
}
