ref #65, new formula functions: CORREL, SUMX2MY2, SUMX2PY2, and SUMXMY2

2 files changed, 121 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index c3b8bf9..2febdeb 100644
--- a/calc.go
+++ b/calc.go
@@ -358,6 +358,7 @@ type formulaFuncs struct {
 //    CONCATENATE
 //    CONFIDENCE
 //    CONFIDENCE.NORM
+//    CORREL
 //    COS
 //    COSH
 //    COT
@@ -603,6 +604,9 @@ type formulaFuncs struct {
 //    SUM
 //    SUMIF
 //    SUMSQ
+//    SUMX2MY2
+//    SUMX2PY2
+//    SUMXMY2
 //    SWITCH
 //    SYD
 //    T
@@ -4945,6 +4949,63 @@ func (fn *formulaFuncs) SUMSQ(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(sq)
 }
 
+// sumx is an implementation of the formula functions SUMX2MY2, SUMX2PY2 and
+// SUMXMY2.
+func (fn *formulaFuncs) sumx(name string, argsList *list.List) formulaArg {
+	if argsList.Len() != 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 2 arguments", name))
+	}
+	array1 := argsList.Front().Value.(formulaArg)
+	array2 := argsList.Back().Value.(formulaArg)
+	left, right := array1.ToList(), array2.ToList()
+	n := len(left)
+	if n != len(right) {
+		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+	}
+	result := 0.0
+	for i := 0; i < n; i++ {
+		if lhs, rhs := left[i].ToNumber(), right[i].ToNumber(); lhs.Number != 0 && rhs.Number != 0 {
+			switch name {
+			case "SUMX2MY2":
+				result += lhs.Number*lhs.Number - rhs.Number*rhs.Number
+			case "SUMX2PY2":
+				result += lhs.Number*lhs.Number + rhs.Number*rhs.Number
+			default:
+				result += (lhs.Number - rhs.Number) * (lhs.Number - rhs.Number)
+			}
+		}
+	}
+	return newNumberFormulaArg(result)
+}
+
+// SUMX2MY2 function returns the sum of the differences of squares of two
+// supplied sets of values. The syntax of the function is:
+//
+//    SUMX2MY2(array_x,array_y)
+//
+func (fn *formulaFuncs) SUMX2MY2(argsList *list.List) formulaArg {
+	return fn.sumx("SUMX2MY2", argsList)
+}
+
+// SUMX2PY2 function returns the sum of the sum of squares of two supplied sets
+// of values. The syntax of the function is:
+//
+//    SUMX2PY2(array_x,array_y)
+//
+func (fn *formulaFuncs) SUMX2PY2(argsList *list.List) formulaArg {
+	return fn.sumx("SUMX2PY2", argsList)
+}
+
+// SUMXMY2 function returns the sum of the squares of differences between
+// corresponding values in two supplied arrays. The syntax of the function
+// is:
+//
+//    SUMXMY2(array_x,array_y)
+//
+func (fn *formulaFuncs) SUMXMY2(argsList *list.List) formulaArg {
+	return fn.sumx("SUMXMY2", argsList)
+}
+
 // TAN function calculates the tangent of a given angle. The syntax of the
 // function is:
 //
@@ -5306,6 +5367,45 @@ func (fn *formulaFuncs) countSum(countText bool, args []formulaArg) (count, sum
 	return
 }
 
+// CORREL function calculates the Pearson Product-Moment Correlation
+// Coefficient for two sets of values. The syntax of the function is:
+//
+//    CORREL(array1,array2)
+//
+func (fn *formulaFuncs) CORREL(argsList *list.List) formulaArg {
+	if argsList.Len() != 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "CORREL requires 2 arguments")
+	}
+	array1 := argsList.Front().Value.(formulaArg)
+	array2 := argsList.Back().Value.(formulaArg)
+	left, right := array1.ToList(), array2.ToList()
+	n := len(left)
+	if n != len(right) {
+		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+	}
+	l1, l2, l3 := list.New(), list.New(), list.New()
+	for i := 0; i < n; i++ {
+		if lhs, rhs := left[i].ToNumber(), right[i].ToNumber(); lhs.Number != 0 && rhs.Number != 0 {
+			l1.PushBack(lhs)
+			l2.PushBack(rhs)
+		}
+	}
+	stdev1, stdev2 := fn.STDEV(l1), fn.STDEV(l2)
+	if stdev1.Number == 0 || stdev2.Number == 0 {
+		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
+	}
+	mean1, mean2, skip := fn.AVERAGE(l1), fn.AVERAGE(l2), 0
+	for i := 0; i < n; i++ {
+		lhs, rhs := left[i].ToNumber(), right[i].ToNumber()
+		if lhs.Number == 0 || rhs.Number == 0 {
+			skip++
+			continue
+		}
+		l3.PushBack(newNumberFormulaArg((lhs.Number - mean1.Number) * (rhs.Number - mean2.Number)))
+	}
+	return newNumberFormulaArg(fn.SUM(l3).Number / float64(n-skip-1) / stdev1.Number / stdev2.Number)
+}
+
 // COUNT function returns the count of numeric values in a supplied set of
 // cells or values. This count includes both numbers and dates. The syntax of
 // the function is:
diff --git a/calc_test.go b/calc_test.go
index 3749702..fa216c9 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -747,6 +747,12 @@ func TestCalcCellValue(t *testing.T) {
 		`=SUMSQ("",A1,B1,A2,B2,6)`: "82",
 		`=SUMSQ(1,SUMSQ(1))`:       "2",
 		"=SUMSQ(MUNIT(3))":         "0",
+		// SUMX2MY2
+		"=SUMX2MY2(A1:A4,B1:B4)": "-36",
+		// SUMX2PY2
+		"=SUMX2PY2(A1:A4,B1:B4)": "46",
+		// SUMXMY2
+		"=SUMXMY2(A1:A4,B1:B4)": "18",
 		// TAN
 		"=TAN(1.047197551)": "1.732050806782486",
 		"=TAN(0)":           "0",
@@ -785,6 +791,8 @@ func TestCalcCellValue(t *testing.T) {
 		"=CONFIDENCE(0.05,0.07,100)": "0.0137197479028414",
 		// CONFIDENCE.NORM
 		"=CONFIDENCE.NORM(0.05,0.07,100)": "0.0137197479028414",
+		// CORREL
+		"=CORREL(A1:A5,B1:B5)": "1",
 		// COUNT
 		"=COUNT()":                        "0",
 		"=COUNT(E1:F2,\"text\",1,INT(2))": "3",
@@ -2238,6 +2246,15 @@ func TestCalcCellValue(t *testing.T) {
 		// SUMSQ
 		`=SUMSQ("X")`:   "strconv.ParseFloat: parsing \"X\": invalid syntax",
 		"=SUMSQ(C1:D2)": "strconv.ParseFloat: parsing \"Month\": invalid syntax",
+		// SUMX2MY2
+		"=SUMX2MY2()":         "SUMX2MY2 requires 2 arguments",
+		"=SUMX2MY2(A1,B1:B2)": "#N/A",
+		// SUMX2PY2
+		"=SUMX2PY2()":         "SUMX2PY2 requires 2 arguments",
+		"=SUMX2PY2(A1,B1:B2)": "#N/A",
+		// SUMXMY2
+		"=SUMXMY2()":         "SUMXMY2 requires 2 arguments",
+		"=SUMXMY2(A1,B1:B2)": "#N/A",
 		// TAN
 		"=TAN()":    "TAN requires 1 numeric argument",
 		`=TAN("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
@@ -2284,6 +2301,10 @@ func TestCalcCellValue(t *testing.T) {
 		"=CONFIDENCE.NORM(1,0.07,100)":     "#NUM!",
 		"=CONFIDENCE.NORM(0.05,0,100)":     "#NUM!",
 		"=CONFIDENCE.NORM(0.05,0.07,0.5)":  "#NUM!",
+		// CORREL
+		"=CORREL()":            "CORREL requires 2 arguments",
+		"=CORREL(A1:A3,B1:B5)": "#N/A",
+		"=CORREL(A1:A1,B1:B1)": "#DIV/0!",
 		// COUNTBLANK
 		"=COUNTBLANK()":    "COUNTBLANK requires 1 argument",
 		"=COUNTBLANK(1,2)": "COUNTBLANK requires 1 argument",