diff options
| -rw-r--r-- | calc.go | 57 | ||||
| -rw-r--r-- | calc_test.go | 33 |
2 files changed, 90 insertions, 0 deletions
@@ -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, diff --git a/calc_test.go b/calc_test.go index cd09e97..7018ee9 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1899,6 +1899,20 @@ func TestCalcCellValue(t *testing.T) { // VAR.P "=VAR.P()": "VAR.P requires at least 1 argument", "=VAR.P(\"\")": "#DIV/0!", + // Z.TEST + "Z.TEST(A1)": "Z.TEST requires at least 2 arguments", + "Z.TEST(A1,0,0,0)": "Z.TEST accepts at most 3 arguments", + "Z.TEST(H1,0)": "#N/A", + "Z.TEST(A1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "Z.TEST(A1,1)": "#DIV/0!", + "Z.TEST(A1,1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // ZTEST + "ZTEST(A1)": "ZTEST requires at least 2 arguments", + "ZTEST(A1,0,0,0)": "ZTEST accepts at most 3 arguments", + "ZTEST(H1,0)": "#N/A", + "ZTEST(A1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "ZTEST(A1,1)": "#DIV/0!", + "ZTEST(A1,1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // Information Functions // ISBLANK "=ISBLANK(A1,A2)": "ISBLANK requires 1 argument", @@ -2759,6 +2773,25 @@ func TestCalcMATCH(t *testing.T) { assert.Equal(t, newErrorFormulaArg(formulaErrorNA, formulaErrorNA), calcMatch(2, nil, []formulaArg{})) } +func TestCalcZTEST(t *testing.T) { + f := NewFile() + assert.NoError(t, f.SetSheetRow("Sheet1", "A1", &[]int{4, 5, 2, 5, 8, 9, 3, 2, 3, 8, 9, 5})) + formulaList := map[string]string{ + "=Z.TEST(A1:L1,5)": "0.371103278558538", + "=Z.TEST(A1:L1,6)": "0.838129187019751", + "=Z.TEST(A1:L1,5,1)": "0.193238115385616", + "=ZTEST(A1:L1,5)": "0.371103278558538", + "=ZTEST(A1:L1,6)": "0.838129187019751", + "=ZTEST(A1:L1,5,1)": "0.193238115385616", + } + for formula, expected := range formulaList { + assert.NoError(t, f.SetCellFormula("Sheet1", "M1", formula)) + result, err := f.CalcCellValue("Sheet1", "M1") + assert.NoError(t, err, formula) + assert.Equal(t, expected, result, formula) + } +} + func TestStrToDate(t *testing.T) { _, _, _, _, err := strToDate("") assert.Equal(t, formulaErrorVALUE, err.Error) |