summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go121
-rw-r--r--calc_test.go50
-rw-r--r--crypt.go2
3 files changed, 163 insertions, 10 deletions
diff --git a/calc.go b/calc.go
index c277212..a87fa2f 100644
--- a/calc.go
+++ b/calc.go
@@ -356,6 +356,8 @@ type formulaFuncs struct {
// CHAR
// CHIDIST
// CHIINV
+// CHITEST
+// CHISQ.TEST
// CHOOSE
// CLEAN
// CODE
@@ -1243,7 +1245,7 @@ func isOperatorPrefixToken(token efp.Token) bool {
return (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || (ok && token.TType == efp.TokenTypeOperatorInfix)
}
-// isOperand determine if the token is parse operand perand.
+// isOperand determine if the token is parse operand.
func isOperand(token efp.Token) bool {
return token.TType == efp.TokenTypeOperand && (token.TSubType == efp.TokenSubTypeNumber || token.TSubType == efp.TokenSubTypeText)
}
@@ -4685,7 +4687,7 @@ func (fn *formulaFuncs) SERIESSUM(argsList *list.List) formulaArg {
if num.Type != ArgNumber {
return num
}
- result += num.Number * math.Pow(x.Number, (n.Number+(m.Number*float64(i))))
+ result += num.Number * math.Pow(x.Number, n.Number+(m.Number*i))
i++
}
return newNumberFormulaArg(result)
@@ -6224,7 +6226,7 @@ func (fn *formulaFuncs) BINOMdotDISTdotRANGE(argsList *list.List) formulaArg {
return newNumberFormulaArg(sum)
}
-// binominv implement inverse of the binomial distribution calcuation.
+// binominv implement inverse of the binomial distribution calculation.
func binominv(n, p, alpha float64) float64 {
q, i, sum, max := 1-p, 0.0, 0.0, 0.0
n = math.Floor(n)
@@ -6292,11 +6294,55 @@ func (fn *formulaFuncs) CHIDIST(argsList *list.List) formulaArg {
if x.Type != ArgNumber {
return x
}
- degress := argsList.Back().Value.(formulaArg).ToNumber()
- if degress.Type != ArgNumber {
- return degress
+ degrees := argsList.Back().Value.(formulaArg).ToNumber()
+ if degrees.Type != ArgNumber {
+ return degrees
}
- return newNumberFormulaArg(1 - (incompleteGamma(degress.Number/2, x.Number/2) / math.Gamma(degress.Number/2)))
+ logSqrtPi, sqrtPi := math.Log(math.Sqrt(math.Pi)), 1/math.Sqrt(math.Pi)
+ var e, s, z, c, y float64
+ a, x1, even := x.Number/2, x.Number, int(degrees.Number)%2 == 0
+ if degrees.Number > 1 {
+ y = math.Exp(-a)
+ }
+ args := list.New()
+ args.PushBack(newNumberFormulaArg(-math.Sqrt(x1)))
+ o := fn.NORMSDIST(args)
+ s = 2 * o.Number
+ if even {
+ s = y
+ }
+ if degrees.Number > 2 {
+ x1 = (degrees.Number - 1) / 2
+ z = 0.5
+ if even {
+ z = 1
+ }
+ if a > 20 {
+ e = logSqrtPi
+ if even {
+ e = 0
+ }
+ c = math.Log(a)
+ for z <= x1 {
+ e = math.Log(z) + e
+ s += math.Exp(c*z - a - e)
+ z += 1
+ }
+ return newNumberFormulaArg(s)
+ }
+ e = sqrtPi / math.Sqrt(a)
+ if even {
+ e = 1
+ }
+ c = 0
+ for z <= x1 {
+ e = e * (a / z)
+ c = c + e
+ z += 1
+ }
+ return newNumberFormulaArg(c*y + s)
+ }
+ return newNumberFormulaArg(s)
}
// CHIINV function calculates the inverse of the right-tailed probability of
@@ -6325,6 +6371,65 @@ func (fn *formulaFuncs) CHIINV(argsList *list.List) formulaArg {
return newNumberFormulaArg(gammainv(1-probability.Number, 0.5*deg.Number, 2.0))
}
+// CHITEST function uses the chi-square test to calculate the probability that
+// the differences between two supplied data sets (of observed and expected
+// frequencies), are likely to be simply due to sampling error, or if they are
+// likely to be real. The syntax of the function is:
+//
+// CHITEST(actual_range,expected_range)
+//
+func (fn *formulaFuncs) CHITEST(argsList *list.List) formulaArg {
+ if argsList.Len() != 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "CHITEST requires 2 arguments")
+ }
+ actual, expected := argsList.Front().Value.(formulaArg), argsList.Back().Value.(formulaArg)
+ actualList, expectedList := actual.ToList(), expected.ToList()
+ rows := len(actual.Matrix)
+ columns := len(actualList) / rows
+ if len(actualList) != len(expectedList) || len(actualList) == 1 {
+ return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+ }
+ var result float64
+ var degrees int
+ for i := 0; i < len(actualList); i++ {
+ a, e := actualList[i].ToNumber(), expectedList[i].ToNumber()
+ if a.Type == ArgNumber && e.Type == ArgNumber {
+ if e.Number == 0 {
+ return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
+ }
+ if e.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ result += (a.Number - e.Number) * (a.Number - e.Number) / e.Number
+ }
+ }
+ if rows == 1 {
+ degrees = columns - 1
+ } else if columns == 1 {
+ degrees = rows - 1
+ } else {
+ degrees = (columns - 1) * (rows - 1)
+ }
+ args := list.New()
+ args.PushBack(newNumberFormulaArg(result))
+ args.PushBack(newNumberFormulaArg(float64(degrees)))
+ return fn.CHIDIST(args)
+}
+
+// CHISQdotTEST function performs the chi-square test on two supplied data sets
+// (of observed and expected frequencies), and returns the probability that
+// the differences between the sets are simply due to sampling error. The
+// syntax of the function is:
+//
+// CHISQ.TEST(actual_range,expected_range)
+//
+func (fn *formulaFuncs) CHISQdotTEST(argsList *list.List) formulaArg {
+ if argsList.Len() != 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "CHISQ.TEST requires 2 arguments")
+ }
+ return fn.CHITEST(argsList)
+}
+
// confidence is an implementation of the formula functions CONFIDENCE and
// CONFIDENCE.NORM.
func (fn *formulaFuncs) confidence(name string, argsList *list.List) formulaArg {
@@ -7096,7 +7201,7 @@ func (fn *formulaFuncs) EXPONDIST(argsList *list.List) formulaArg {
// FdotDIST function calculates the Probability Density Function or the
// Cumulative Distribution Function for the F Distribution. This function is
-// frequently used used to measure the degree of diversity between two data
+// frequently used to measure the degree of diversity between two data
// sets. The syntax of the function is:
//
// F.DIST(x,deg_freedom1,deg_freedom2,cumulative)
diff --git a/calc_test.go b/calc_test.go
index f9da26d..308db55 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -843,7 +843,9 @@ func TestCalcCellValue(t *testing.T) {
"=BINOM.INV(100,0.5,90%)": "56",
// CHIDIST
"=CHIDIST(0.5,3)": "0.918891411654676",
- "=CHIDIST(8,3)": "0.0460117056892315",
+ "=CHIDIST(8,3)": "0.0460117056892314",
+ "=CHIDIST(40,4)": "4.32842260712097E-08",
+ "=CHIDIST(42,4)": "1.66816329414062E-08",
// CHIINV
"=CHIINV(0.5,1)": "0.454936423119572",
"=CHIINV(0.75,1)": "0.101531044267622",
@@ -4213,6 +4215,52 @@ func TestCalcHLOOKUP(t *testing.T) {
}
}
+func TestCalcCHITESTandCHISQdotTEST(t *testing.T) {
+ cellData := [][]interface{}{
+ {nil, "Observed Frequencies", nil, nil, "Expected Frequencies"},
+ {nil, "men", "women", nil, nil, "men", "women"},
+ {"answer a", 33, 39, nil, "answer a", 26.25, 31.5},
+ {"answer b", 62, 62, nil, "answer b", 57.75, 61.95},
+ {"answer c", 10, 4, nil, "answer c", 21, 11.55},
+ {nil, -1, 0},
+ }
+ f := prepareCalcData(cellData)
+ formulaList := map[string]string{
+ "=CHITEST(B3:C5,F3:G5)": "0.000699102758787672",
+ "=CHITEST(B3:C3,F3:G3)": "0.0605802098655177",
+ "=CHITEST(B3:B4,F3:F4)": "0.152357748933542",
+ "=CHITEST(B4:B6,F3:F5)": "7.07076951440726E-25",
+ "=CHISQ.TEST(B3:C5,F3:G5)": "0.000699102758787672",
+ "=CHISQ.TEST(B3:C3,F3:G3)": "0.0605802098655177",
+ "=CHISQ.TEST(B3:B4,F3:F4)": "0.152357748933542",
+ "=CHISQ.TEST(B4:B6,F3:F5)": "7.07076951440726E-25",
+ }
+ for formula, expected := range formulaList {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "I1", formula))
+ result, err := f.CalcCellValue("Sheet1", "I1")
+ assert.NoError(t, err, formula)
+ assert.Equal(t, expected, result, formula)
+ }
+ calcError := map[string]string{
+ "=CHITEST()": "CHITEST requires 2 arguments",
+ "=CHITEST(B3:C5,F3:F4)": "#N/A",
+ "=CHITEST(B3:B3,F3:F3)": "#N/A",
+ "=CHITEST(F3:F5,B4:B6)": "#NUM!",
+ "=CHITEST(F3:F5,C4:C6)": "#DIV/0!",
+ "=CHISQ.TEST()": "CHISQ.TEST requires 2 arguments",
+ "=CHISQ.TEST(B3:C5,F3:F4)": "#N/A",
+ "=CHISQ.TEST(B3:B3,F3:F3)": "#N/A",
+ "=CHISQ.TEST(F3:F5,B4:B6)": "#NUM!",
+ "=CHISQ.TEST(F3:F5,C4:C6)": "#DIV/0!",
+ }
+ for formula, expected := range calcError {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "I1", formula))
+ result, err := f.CalcCellValue("Sheet1", "I1")
+ 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)
diff --git a/crypt.go b/crypt.go
index 8a783a9..da9feb4 100644
--- a/crypt.go
+++ b/crypt.go
@@ -128,7 +128,7 @@ type StandardEncryptionVerifier struct {
EncryptedVerifierHash []byte
}
-// Decrypt API decrypt the CFB file format with ECMA-376 agile encryption and
+// Decrypt API decrypts the CFB file format with ECMA-376 agile encryption and
// standard encryption. Support cryptographic algorithm: MD4, MD5, RIPEMD-160,
// SHA1, SHA256, SHA384 and SHA512 currently.
func Decrypt(raw []byte, opt *Options) (packageBuf []byte, err error) {
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 09:45:52 +0000