diff options
| -rw-r--r-- | calc.go | 62 | ||||
| -rw-r--r-- | calc_test.go | 12 |
2 files changed, 74 insertions, 0 deletions
@@ -584,6 +584,7 @@ type formulaFuncs struct { // ODDFPRICE // OR // PDURATION +// PEARSON // PERCENTILE.EXC // PERCENTILE.INC // PERCENTILE @@ -628,6 +629,7 @@ type formulaFuncs struct { // ROW // ROWS // RRI +// RSQ // SEC // SECH // SECOND @@ -8858,6 +8860,56 @@ func (fn *formulaFuncs) min(mina bool, argsList *list.List) formulaArg { return newNumberFormulaArg(min) } +// pearsonProduct is an implementation of the formula functions PEARSON and +// RSQ. +func (fn *formulaFuncs) pearsonProduct(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).ToList() + array2 := argsList.Back().Value.(formulaArg).ToList() + if len(array1) != len(array2) { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + var sum, deltaX, deltaY, x, y, length float64 + for i := 0; i < len(array1); i++ { + num1, num2 := array1[i].ToNumber(), array2[i].ToNumber() + if !(num1.Type == ArgNumber && num2.Type == ArgNumber) { + continue + } + x += num1.Number + y += num2.Number + length++ + } + x /= length + y /= length + for i := 0; i < len(array1); i++ { + num1, num2 := array1[i].ToNumber(), array2[i].ToNumber() + if !(num1.Type == ArgNumber && num2.Type == ArgNumber) { + continue + } + sum += (num1.Number - x) * (num2.Number - y) + deltaX += (num1.Number - x) * (num1.Number - x) + deltaY += (num2.Number - y) * (num2.Number - y) + } + if deltaX == 0 || deltaY == 0 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + if name == "RSQ" { + return newNumberFormulaArg(math.Pow(sum/math.Sqrt(deltaX*deltaY), 2)) + } + return newNumberFormulaArg(sum / math.Sqrt(deltaX*deltaY)) +} + +// PEARSON function calculates the Pearson Product-Moment Correlation +// Coefficient for two sets of values. The syntax of the function is: +// +// PEARSON(array1,array2) +// +func (fn *formulaFuncs) PEARSON(argsList *list.List) formulaArg { + return fn.pearsonProduct("PEARSON", argsList) +} + // PERCENTILEdotEXC function returns the k'th percentile (i.e. the value below // which k% of the data values fall) for a supplied range of values and a // supplied k (between 0 & 1 exclusive).The syntax of the function is: @@ -9206,6 +9258,16 @@ func (fn *formulaFuncs) RANK(argsList *list.List) formulaArg { return fn.rank("RANK", argsList) } +// RSQ function calculates the square of the Pearson Product-Moment Correlation +// Coefficient for two supplied sets of values. The syntax of the function +// is: +// +// RSQ(known_y's,known_x's) +// +func (fn *formulaFuncs) RSQ(argsList *list.List) formulaArg { + return fn.pearsonProduct("RSQ", argsList) +} + // SKEW function calculates the skewness of the distribution of a supplied set // of values. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index 52bc061..a7f88b0 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1095,6 +1095,8 @@ func TestCalcCellValue(t *testing.T) { "=MINA(A1:B4,MUNIT(1),INT(0),1,E1:F2,\"\")": "0", // MINIFS "=MINIFS(F2:F4,A2:A4,\">0\")": "22100", + // PEARSON + "=PEARSON(A1:A4,B1:B4)": "1", // PERCENTILE.EXC "=PERCENTILE.EXC(A1:A4,0.2)": "0", "=PERCENTILE.EXC(A1:A4,0.6)": "2", @@ -1149,6 +1151,8 @@ func TestCalcCellValue(t *testing.T) { "=RANK.EQ(1,A1:B5)": "5", "=RANK.EQ(1,A1:B5,0)": "5", "=RANK.EQ(1,A1:B5,1)": "2", + // RSQ + "=RSQ(A1:A4,B1:B4)": "1", // SKEW "=SKEW(1,2,3,4,3)": "-0.404796008910937", "=SKEW(A1:B2)": "0", @@ -2974,6 +2978,10 @@ func TestCalcCellValue(t *testing.T) { // MINIFS "=MINIFS()": "MINIFS requires at least 3 arguments", "=MINIFS(F2:F4,A2:A4,\"<0\",D2:D9)": "#N/A", + // PEARSON + "=PEARSON()": "PEARSON requires 2 arguments", + "=PEARSON(A1:A2,B1:B1)": "#N/A", + "=PEARSON(A4,A4)": "#DIV/0!", // PERCENTILE.EXC "=PERCENTILE.EXC()": "PERCENTILE.EXC requires 2 arguments", "=PERCENTILE.EXC(A1:A4,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", @@ -3047,6 +3055,10 @@ func TestCalcCellValue(t *testing.T) { "=RANK.EQ(-1,A1:B5)": "#N/A", "=RANK.EQ(\"\",A1:B5)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=RANK.EQ(1,A1:B5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // RSQ + "=RSQ()": "RSQ requires 2 arguments", + "=RSQ(A1:A2,B1:B1)": "#N/A", + "=RSQ(A4,A4)": "#DIV/0!", // SKEW "=SKEW()": "SKEW requires at least 1 argument", "=SKEW(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", |