diff options
| -rw-r--r-- | calc.go | 38 | ||||
| -rw-r--r-- | calc_test.go | 36 |
2 files changed, 74 insertions, 0 deletions
@@ -674,6 +674,7 @@ type formulaFuncs struct { // STDEVA // STDEVP // STDEVPA +// STEYX // SUBSTITUTE // SUM // SUMIF @@ -10647,6 +10648,43 @@ func (fn *formulaFuncs) STDEVPA(argsList *list.List) formulaArg { return fn.stdevp("STDEVPA", argsList) } +// STEYX function calculates the standard error for the line of best fit, +// through a supplied set of x- and y- values. The syntax of the function is: +// +// STEYX(known_y's,known_x's) +// +func (fn *formulaFuncs) STEYX(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "STEYX requires 2 arguments") + } + array1 := argsList.Back().Value.(formulaArg).ToList() + array2 := argsList.Front().Value.(formulaArg).ToList() + if len(array1) != len(array2) { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + var count, sumX, sumY, squareX, squareY, sigmaXY float64 + for i := 0; i < len(array1); i++ { + num1, num2 := array1[i].ToNumber(), array2[i].ToNumber() + if !(num1.Type == ArgNumber && num2.Type == ArgNumber) { + continue + } + sumX += num1.Number + sumY += num2.Number + squareX += num1.Number * num1.Number + squareY += num2.Number * num2.Number + sigmaXY += num1.Number * num2.Number + count++ + } + if count < 3 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + dx, dy := sumX/count, sumY/count + sigma1 := squareY - 2*dy*sumY + count*dy*dy + sigma2 := sigmaXY - dy*sumX - sumY*dx + count*dy*dx + sigma3 := squareX - 2*dx*sumX + count*dx*dx + return newNumberFormulaArg(math.Sqrt((sigma1 - (sigma2*sigma2)/sigma3) / (count - 2))) +} + // getTDist is an implementation for the beta distribution probability density // function. func getTDist(T, fDF, nType float64) float64 { diff --git a/calc_test.go b/calc_test.go index c9891a3..92460d7 100644 --- a/calc_test.go +++ b/calc_test.go @@ -5325,6 +5325,42 @@ func TestCalcSHEETS(t *testing.T) { } } +func TestCalcSTEY(t *testing.T) { + cellData := [][]interface{}{ + {"known_x's", "known_y's"}, + {1, 3}, + {2, 7.9}, + {3, 8}, + {4, 9.2}, + {4.5, 12}, + {5, 10.5}, + {6, 15}, + {7, 15.5}, + {8, 17}, + } + f := prepareCalcData(cellData) + formulaList := map[string]string{ + "=STEYX(B2:B11,A2:A11)": "1.20118634668221", + } + 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{ + "=STEYX()": "STEYX requires 2 arguments", + "=STEYX(B2:B11,A1:A9)": "#N/A", + "=STEYX(B2,A2)": "#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 TestCalcTTEST(t *testing.T) { cellData := [][]interface{}{ {4, 8, nil, 1, 1}, |