ref #65, new formula functions: HYPGEOM.DIST and HYPGEOMDIST

3 files changed, 136 insertions, 3 deletions

diff --git a/calc.go b/calc.go
index 0f2003d..fc2895f 100644
--- a/calc.go
+++ b/calc.go
@@ -464,6 +464,8 @@ type formulaFuncs struct {
 //    HEX2OCT
 //    HLOOKUP
 //    HOUR
+//    HYPGEOM.DIST
+//    HYPGEOMDIST
 //    IF
 //    IFERROR
 //    IFNA
@@ -7328,6 +7330,90 @@ func (fn *formulaFuncs) HARMEAN(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(1 / (val / cnt))
 }
 
+// prepareHYPGEOMDISTArgs checking and prepare arguments for the formula
+// function HYPGEOMDIST and HYPGEOM.DIST.
+func (fn *formulaFuncs) prepareHYPGEOMDISTArgs(name string, argsList *list.List) formulaArg {
+	if name == "HYPGEOMDIST" && argsList.Len() != 4 {
+		return newErrorFormulaArg(formulaErrorVALUE, "HYPGEOMDIST requires 4 numeric arguments")
+	}
+	if name == "HYPGEOM.DIST" && argsList.Len() != 5 {
+		return newErrorFormulaArg(formulaErrorVALUE, "HYPGEOM.DIST requires 5 arguments")
+	}
+	var sampleS, numberSample, populationS, numberPop, cumulative formulaArg
+	if sampleS = argsList.Front().Value.(formulaArg).ToNumber(); sampleS.Type != ArgNumber {
+		return sampleS
+	}
+	if numberSample = argsList.Front().Next().Value.(formulaArg).ToNumber(); numberSample.Type != ArgNumber {
+		return numberSample
+	}
+	if populationS = argsList.Front().Next().Next().Value.(formulaArg).ToNumber(); populationS.Type != ArgNumber {
+		return populationS
+	}
+	if numberPop = argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber(); numberPop.Type != ArgNumber {
+		return numberPop
+	}
+	if sampleS.Number < 0 ||
+		sampleS.Number > math.Min(numberSample.Number, populationS.Number) ||
+		sampleS.Number < math.Max(0, numberSample.Number-numberPop.Number+populationS.Number) ||
+		numberSample.Number <= 0 ||
+		numberSample.Number > numberPop.Number ||
+		populationS.Number <= 0 ||
+		populationS.Number > numberPop.Number ||
+		numberPop.Number <= 0 {
+		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+	}
+	if name == "HYPGEOM.DIST" {
+		if cumulative = argsList.Back().Value.(formulaArg).ToBool(); cumulative.Type != ArgNumber {
+			return cumulative
+		}
+	}
+	return newListFormulaArg([]formulaArg{sampleS, numberSample, populationS, numberPop, cumulative})
+}
+
+// HYPGEOMdotDIST function returns the value of the hypergeometric distribution
+// for a specified number of successes from a population sample. The function
+// can calculate the cumulative distribution or the probability density
+// function. The syntax of the function is:
+//
+//    HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)
+//
+func (fn *formulaFuncs) HYPGEOMdotDIST(argsList *list.List) formulaArg {
+	args := fn.prepareHYPGEOMDISTArgs("HYPGEOM.DIST", argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	sampleS, numberSample, populationS, numberPop, cumulative := args.List[0], args.List[1], args.List[2], args.List[3], args.List[4]
+	if cumulative.Number == 1 {
+		var res float64
+		for i := 0; i <= int(sampleS.Number); i++ {
+			res += binomCoeff(populationS.Number, float64(i)) *
+				binomCoeff(numberPop.Number-populationS.Number, numberSample.Number-float64(i)) /
+				binomCoeff(numberPop.Number, numberSample.Number)
+		}
+		return newNumberFormulaArg(res)
+	}
+	return newNumberFormulaArg(binomCoeff(populationS.Number, sampleS.Number) *
+		binomCoeff(numberPop.Number-populationS.Number, numberSample.Number-sampleS.Number) /
+		binomCoeff(numberPop.Number, numberSample.Number))
+}
+
+// HYPGEOMDIST function returns the value of the hypergeometric distribution
+// for a given number of successes from a sample of a population. The syntax
+// of the function is:
+//
+//    HYPGEOMDIST(sample_s,number_sample,population_s,number_pop)
+//
+func (fn *formulaFuncs) HYPGEOMDIST(argsList *list.List) formulaArg {
+	args := fn.prepareHYPGEOMDISTArgs("HYPGEOMDIST", argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	sampleS, numberSample, populationS, numberPop := args.List[0], args.List[1], args.List[2], args.List[3]
+	return newNumberFormulaArg(binomCoeff(populationS.Number, sampleS.Number) *
+		binomCoeff(numberPop.Number-populationS.Number, numberSample.Number-sampleS.Number) /
+		binomCoeff(numberPop.Number, numberSample.Number))
+}
+
 // KURT function calculates the kurtosis of a supplied set of values. The
 // syntax of the function is:
 //
diff --git a/calc_test.go b/calc_test.go
index fb91e2e..30db8cf 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -945,6 +945,20 @@ func TestCalcCellValue(t *testing.T) {
 		// HARMEAN
 		"=HARMEAN(2.5,3,0.5,1,3)":               "1.22950819672131",
 		"=HARMEAN(\"2.5\",3,0.5,1,INT(3),\"\")": "1.22950819672131",
+		// HYPGEOM.DIST
+		"=HYPGEOM.DIST(0,3,3,9,TRUE)":   "0.238095238095238",
+		"=HYPGEOM.DIST(1,3,3,9,TRUE)":   "0.773809523809524",
+		"=HYPGEOM.DIST(2,3,3,9,TRUE)":   "0.988095238095238",
+		"=HYPGEOM.DIST(3,3,3,9,TRUE)":   "1",
+		"=HYPGEOM.DIST(1,4,4,12,FALSE)": "0.452525252525253",
+		"=HYPGEOM.DIST(2,4,4,12,FALSE)": "0.339393939393939",
+		"=HYPGEOM.DIST(3,4,4,12,FALSE)": "0.0646464646464646",
+		"=HYPGEOM.DIST(4,4,4,12,FALSE)": "0.00202020202020202",
+		// HYPGEOMDIST
+		"=HYPGEOMDIST(1,4,4,12)": "0.452525252525253",
+		"=HYPGEOMDIST(2,4,4,12)": "0.339393939393939",
+		"=HYPGEOMDIST(3,4,4,12)": "0.0646464646464646",
+		"=HYPGEOMDIST(4,4,4,12)": "0.00202020202020202",
 		// KURT
 		"=KURT(F1:F9)":           "-1.03350350255137",
 		"=KURT(F1,F2:F9)":        "-1.03350350255137",
@@ -2652,6 +2666,7 @@ func TestCalcCellValue(t *testing.T) {
 		// GAMMALN.PRECISE
 		"=GAMMALN.PRECISE()":     "GAMMALN.PRECISE requires 1 numeric argument",
 		"=GAMMALN.PRECISE(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=GAMMALN.PRECISE(0)":    "#NUM!",
 		// GAUSS
 		"=GAUSS()":     "GAUSS requires 1 numeric argument",
 		"=GAUSS(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
@@ -2663,6 +2678,35 @@ func TestCalcCellValue(t *testing.T) {
 		"=HARMEAN()":   "HARMEAN requires at least 1 argument",
 		"=HARMEAN(-1)": "#N/A",
 		"=HARMEAN(0)":  "#N/A",
+		// HYPGEOM.DIST
+		"=HYPGEOM.DIST()":                  "HYPGEOM.DIST requires 5 arguments",
+		"=HYPGEOM.DIST(\"\",4,4,12,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOM.DIST(1,\"\",4,12,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOM.DIST(1,4,\"\",12,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOM.DIST(1,4,4,\"\",FALSE)":  "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOM.DIST(1,4,4,12,\"\")":     "strconv.ParseBool: parsing \"\": invalid syntax",
+		"=HYPGEOM.DIST(-1,4,4,12,FALSE)":   "#NUM!",
+		"=HYPGEOM.DIST(2,1,4,12,FALSE)":    "#NUM!",
+		"=HYPGEOM.DIST(2,4,1,12,FALSE)":    "#NUM!",
+		"=HYPGEOM.DIST(2,2,2,1,FALSE)":     "#NUM!",
+		"=HYPGEOM.DIST(1,0,4,12,FALSE)":    "#NUM!",
+		"=HYPGEOM.DIST(1,4,4,2,FALSE)":     "#NUM!",
+		"=HYPGEOM.DIST(1,4,0,12,FALSE)":    "#NUM!",
+		"=HYPGEOM.DIST(1,4,4,0,FALSE)":     "#NUM!",
+		// HYPGEOMDIST
+		"=HYPGEOMDIST()":            "HYPGEOMDIST requires 4 numeric arguments",
+		"=HYPGEOMDIST(\"\",4,4,12)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOMDIST(1,\"\",4,12)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOMDIST(1,4,\"\",12)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOMDIST(1,4,4,\"\")":  "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=HYPGEOMDIST(-1,4,4,12)":   "#NUM!",
+		"=HYPGEOMDIST(2,1,4,12)":    "#NUM!",
+		"=HYPGEOMDIST(2,4,1,12)":    "#NUM!",
+		"=HYPGEOMDIST(2,2,2,1)":     "#NUM!",
+		"=HYPGEOMDIST(1,0,4,12)":    "#NUM!",
+		"=HYPGEOMDIST(1,4,4,2)":     "#NUM!",
+		"=HYPGEOMDIST(1,4,0,12)":    "#NUM!",
+		"=HYPGEOMDIST(1,4,4,0)":     "#NUM!",
 		// KURT
 		"=KURT()":          "KURT requires at least 1 argument",
 		"=KURT(F1,INT(1))": "#DIV/0!",
diff --git a/cell.go b/cell.go
index 4b76271..b2818e7 100644
--- a/cell.go
+++ b/cell.go
@@ -672,9 +672,12 @@ type HyperlinkOpts struct {
 
 // SetCellHyperLink provides a function to set cell hyperlink by given
 // worksheet name and link URL address. LinkType defines two types of
-// hyperlink "External" for website or "Location" for moving to one of cell
-// in this workbook. Maximum limit hyperlinks in a worksheet is 65530. The
-// below is example for external link.
+// hyperlink "External" for website or "Location" for moving to one of cell in
+// this workbook. Maximum limit hyperlinks in a worksheet is 65530. This
+// function is only used to set the hyperlink of the cell and doesn't affect
+// the value of the cell. If you need to set the value of the cell, please use
+// the other functions such as `SetCellStyle` or `SetSheetRow`. The below is
+// example for external link.
 //
 //    if err := f.SetCellHyperLink("Sheet1", "A3",
 //        "https://github.com/xuri/excelize", "External"); err != nil {