From 4ef68729f5bf2150b06cbace068412b3dde2d1ae Mon Sep 17 00:00:00 2001
From: xuri <xuri.me@gmail.com>
Date: Mon, 13 Sep 2021 22:40:38 +0800
Subject: new formula functions: Z.TEST and ZTEST, ref #65

---
 calc.go | 57 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)

(limited to 'calc.go')

diff --git a/calc.go b/calc.go
index 59d97e0..fa6ec0a 100644
--- a/calc.go
+++ b/calc.go
@@ -506,6 +506,8 @@ type formulaFuncs struct {
 //    VLOOKUP
 //    XOR
 //    YEAR
+//    Z.TEST
+//    ZTEST
 //
 func (f *File) CalcCellValue(sheet, cell string) (result string, err error) {
 	var (
@@ -5612,6 +5614,61 @@ func (fn *formulaFuncs) VARdotP(argsList *list.List) formulaArg {
 	return fn.VARP(argsList)
 }
 
+// ZdotTEST function calculates the one-tailed probability value of the
+// Z-Test. The syntax of the function is:
+//
+//    Z.TEST(array,x,[sigma])
+//
+func (fn *formulaFuncs) ZdotTEST(argsList *list.List) formulaArg {
+	argsLen := argsList.Len()
+	if argsLen < 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "Z.TEST requires at least 2 arguments")
+	}
+	if argsLen > 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "Z.TEST accepts at most 3 arguments")
+	}
+	return fn.ZTEST(argsList)
+}
+
+// ZTEST function calculates the one-tailed probability value of the Z-Test.
+// The syntax of the function is:
+//
+//    ZTEST(array,x,[sigma])
+//
+func (fn *formulaFuncs) ZTEST(argsList *list.List) formulaArg {
+	argsLen := argsList.Len()
+	if argsLen < 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "ZTEST requires at least 2 arguments")
+	}
+	if argsLen > 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "ZTEST accepts at most 3 arguments")
+	}
+	arrArg, arrArgs := argsList.Front().Value.(formulaArg), list.New()
+	arrArgs.PushBack(arrArg)
+	arr := fn.AVERAGE(arrArgs)
+	if arr.Type == ArgError {
+		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+	}
+	x := argsList.Front().Next().Value.(formulaArg).ToNumber()
+	if x.Type == ArgError {
+		return x
+	}
+	sigma := argsList.Back().Value.(formulaArg).ToNumber()
+	if sigma.Type == ArgError {
+		return sigma
+	}
+	if argsLen != 3 {
+		sigma = fn.STDEV(arrArgs).ToNumber()
+	}
+	normsdistArg := list.New()
+	div := sigma.Number / math.Sqrt(float64(len(arrArg.ToList())))
+	if div == 0 {
+		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
+	}
+	normsdistArg.PushBack(newNumberFormulaArg((arr.Number - x.Number) / div))
+	return newNumberFormulaArg(1 - fn.NORMSDIST(normsdistArg).Number)
+}
+
 // Information Functions
 
 // ISBLANK function tests if a specified cell is blank (empty) and if so,
-- 
cgit v1.2.1