From 7819cd7fec50513786a5d47c6f11a59cceba541a Mon Sep 17 00:00:00 2001
From: xuri <xuri.me@gmail.com>
Date: Mon, 20 Jun 2022 22:05:23 +0800
Subject: ref #65, new formula function: STEYX

---
 calc.go      | 38 ++++++++++++++++++++++++++++++++++++++
 calc_test.go | 36 ++++++++++++++++++++++++++++++++++++
 2 files changed, 74 insertions(+)

diff --git a/calc.go b/calc.go
index 086c288..6aaf79e 100644
--- a/calc.go
+++ b/calc.go
@@ -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},
-- 
cgit v1.2.1