diff options
| -rw-r--r-- | calc.go | 81 | ||||
| -rw-r--r-- | calc_test.go | 45 |
2 files changed, 122 insertions, 4 deletions
@@ -443,6 +443,8 @@ type formulaFuncs struct { // FLOOR.MATH // FLOOR.PRECISE // FORMULATEXT +// F.TEST +// FTEST // FV // FVSCHEDULE // GAMMA @@ -6468,7 +6470,7 @@ func getGammaContFraction(fA, fX float64) float64 { fQk = math.Nextafter(f3, f3) - math.Nextafter(f4, f4) ) if fQk != 0 { - var fR = fPk / fQk + fR := fPk / fQk bFinished = math.Abs((fApprox-fR)/fR) <= fHalfMachEps fApprox = fR } @@ -6486,8 +6488,8 @@ func getGammaContFraction(fA, fX float64) float64 { // getLogGammaHelper is a part of implementation of the function getLogGamma. func getLogGammaHelper(fZ float64) float64 { - var _fg = 6.024680040776729583740234375 - var zgHelp = fZ + _fg - 0.5 + _fg := 6.024680040776729583740234375 + zgHelp := fZ + _fg - 0.5 return math.Log(getLanczosSum(fZ)) + (fZ-0.5)*math.Log(zgHelp) - zgHelp } @@ -6511,7 +6513,7 @@ func getGammaHelper(fZ float64) float64 { // getLogGamma calculates the natural logarithm of the gamma function. func getLogGamma(fZ float64) float64 { - var fMaxGammaArgument = 171.624376956302 + fMaxGammaArgument := 171.624376956302 if fZ >= fMaxGammaArgument { return getLogGammaHelper(fZ) } @@ -7578,6 +7580,77 @@ func (fn *formulaFuncs) FINV(argsList *list.List) formulaArg { return newNumberFormulaArg((1/calcBetainv(1-(1-probability.Number), d2.Number/2, d1.Number/2, 0, 1) - 1) * (d2.Number / d1.Number)) } +// FdotTEST function returns the F-Test for two supplied arrays. I.e. the +// function returns the two-tailed probability that the variances in the two +// supplied arrays are not significantly different. The syntax of the Ftest +// function is: +// +// F.TEST(array1,array2) +// +func (fn *formulaFuncs) FdotTEST(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "F.TEST requires 2 arguments") + } + array1 := argsList.Front().Value.(formulaArg) + array2 := argsList.Back().Value.(formulaArg) + left, right := array1.ToList(), array2.ToList() + collectMatrix := func(args []formulaArg) (n, accu float64) { + var p, sum float64 + for _, arg := range args { + if num := arg.ToNumber(); num.Type == ArgNumber { + x := num.Number - p + y := x / (n + 1) + p += y + accu += n * x * y + n++ + sum += num.Number + } + } + return + } + nums, accu := collectMatrix(left) + f3 := nums - 1 + if nums == 1 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + f1 := accu / (nums - 1) + if f1 == 0 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + nums, accu = collectMatrix(right) + f4 := nums - 1 + if nums == 1 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + f2 := accu / (nums - 1) + if f2 == 0 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + args := list.New() + args.PushBack(newNumberFormulaArg(f1 / f2)) + args.PushBack(newNumberFormulaArg(f3)) + args.PushBack(newNumberFormulaArg(f4)) + probability := (1 - fn.FDIST(args).Number) * 2 + if probability > 1 { + probability = 2 - probability + } + return newNumberFormulaArg(probability) +} + +// FTEST function returns the F-Test for two supplied arrays. I.e. the function +// returns the two-tailed probability that the variances in the two supplied +// arrays are not significantly different. The syntax of the Ftest function +// is: +// +// FTEST(array1,array2) +// +func (fn *formulaFuncs) FTEST(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "FTEST requires 2 arguments") + } + return fn.FdotTEST(argsList) +} + // LOGINV function calculates the inverse of the Cumulative Log-Normal // Distribution Function of x, for a supplied probability. The syntax of the // function is: diff --git a/calc_test.go b/calc_test.go index 306e24d..fb91e2e 100644 --- a/calc_test.go +++ b/calc_test.go @@ -4292,6 +4292,51 @@ func TestCalcCHITESTandCHISQdotTEST(t *testing.T) { } } +func TestCalcFTEST(t *testing.T) { + cellData := [][]interface{}{ + {"Group 1", "Group 2"}, + {3.5, 9.2}, + {4.7, 8.2}, + {6.2, 7.3}, + {4.9, 6.1}, + {3.8, 5.4}, + {5.5, 7.8}, + {7.1, 5.9}, + {6.7, 8.4}, + {3.9, 7.7}, + {4.6, 6.6}, + } + f := prepareCalcData(cellData) + formulaList := map[string]string{ + "=FTEST(A2:A11,B2:B11)": "0.95403555939413", + "=F.TEST(A2:A11,B2:B11)": "0.95403555939413", + } + for formula, expected := range formulaList { + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.NoError(t, err, formula) + assert.Equal(t, expected, result, formula) + } + calcError := map[string]string{ + "=FTEST()": "FTEST requires 2 arguments", + "=FTEST(A2:A2,B2:B2)": "#DIV/0!", + "=FTEST(A12:A14,B2:B4)": "#DIV/0!", + "=FTEST(A2:A4,B2:B2)": "#DIV/0!", + "=FTEST(A2:A4,B12:B14)": "#DIV/0!", + "=F.TEST()": "F.TEST requires 2 arguments", + "=F.TEST(A2:A2,B2:B2)": "#DIV/0!", + "=F.TEST(A12:A14,B2:B4)": "#DIV/0!", + "=F.TEST(A2:A4,B2:B2)": "#DIV/0!", + "=F.TEST(A2:A4,B12:B14)": "#DIV/0!", + } + for formula, expected := range calcError { + assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) + result, err := f.CalcCellValue("Sheet1", "C1") + assert.EqualError(t, err, expected, formula) + assert.Equal(t, "", result, formula) + } +} + func TestCalcIRR(t *testing.T) { cellData := [][]interface{}{{-1}, {0.2}, {0.24}, {0.288}, {0.3456}, {0.4147}} f := prepareCalcData(cellData) |