summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go76
-rw-r--r--calc_test.go24
2 files changed, 98 insertions, 2 deletions
diff --git a/calc.go b/calc.go
index 657670c..592f315 100644
--- a/calc.go
+++ b/calc.go
@@ -495,6 +495,7 @@ type formulaFuncs struct {
// ODD
// OR
// PDURATION
+// PERCENTILE.EXC
// PERCENTILE.INC
// PERCENTILE
// PERMUT
@@ -510,6 +511,7 @@ type formulaFuncs struct {
// PROPER
// PV
// QUARTILE
+// QUARTILE.EXC
// QUARTILE.INC
// QUOTIENT
// RADIANS
@@ -574,6 +576,7 @@ type formulaFuncs struct {
// VALUE
// VAR
// VAR.P
+// VAR.S
// VARA
// VARP
// VARPA
@@ -5762,6 +5765,43 @@ func (fn *formulaFuncs) min(mina bool, argsList *list.List) formulaArg {
return newNumberFormulaArg(min)
}
+// 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:
+//
+// PERCENTILE.EXC(array,k)
+//
+func (fn *formulaFuncs) PERCENTILEdotEXC(argsList *list.List) formulaArg {
+ if argsList.Len() != 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "PERCENTILE.EXC requires 2 arguments")
+ }
+ array := argsList.Front().Value.(formulaArg).ToList()
+ k := argsList.Back().Value.(formulaArg).ToNumber()
+ if k.Type != ArgNumber {
+ return k
+ }
+ if k.Number <= 0 || k.Number >= 1 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ numbers := []float64{}
+ for _, arg := range array {
+ if arg.Type == ArgError {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ num := arg.ToNumber()
+ if num.Type == ArgNumber {
+ numbers = append(numbers, num.Number)
+ }
+ }
+ cnt := len(numbers)
+ sort.Float64s(numbers)
+ idx := k.Number * (float64(cnt) + 1)
+ base := math.Floor(idx)
+ next := base - 1
+ proportion := idx - base
+ return newNumberFormulaArg(numbers[int(next)] + ((numbers[int(base)] - numbers[int(next)]) * proportion))
+}
+
// PERCENTILEdotINC 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. The syntax of the function is:
@@ -5885,6 +5925,29 @@ func (fn *formulaFuncs) QUARTILE(argsList *list.List) formulaArg {
return fn.PERCENTILE(args)
}
+// QUARTILEdotEXC function returns a requested quartile of a supplied range of
+// values, based on a percentile range of 0 to 1 exclusive. The syntax of the
+// function is:
+//
+// QUARTILE.EXC(array,quart)
+//
+func (fn *formulaFuncs) QUARTILEdotEXC(argsList *list.List) formulaArg {
+ if argsList.Len() != 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "QUARTILE.EXC requires 2 arguments")
+ }
+ quart := argsList.Back().Value.(formulaArg).ToNumber()
+ if quart.Type != ArgNumber {
+ return quart
+ }
+ if quart.Number <= 0 || quart.Number >= 4 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ args := list.New().Init()
+ args.PushBack(argsList.Front().Value.(formulaArg))
+ args.PushBack(newNumberFormulaArg(quart.Number / 4))
+ return fn.PERCENTILEdotEXC(args)
+}
+
// QUARTILEdotINC function returns a requested quartile of a supplied range of
// values. The syntax of the function is:
//
@@ -6102,14 +6165,14 @@ func (fn *formulaFuncs) TRIMMEAN(argsList *list.List) formulaArg {
}
// vars is an implementation of the formula functions VAR, VARA, VARP, VAR.P
-// and VARPA.
+// VAR.S and VARPA.
func (fn *formulaFuncs) vars(name string, argsList *list.List) formulaArg {
if argsList.Len() < 1 {
return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires at least 1 argument", name))
}
summerA, summerB, count := 0.0, 0.0, 0.0
minimum := 0.0
- if name == "VAR" || name == "VARA" {
+ if name == "VAR" || name == "VAR.S" || name == "VARA" {
minimum = 1.0
}
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
@@ -6177,6 +6240,15 @@ func (fn *formulaFuncs) VARdotP(argsList *list.List) formulaArg {
return fn.vars("VAR.P", argsList)
}
+// VARdotS function calculates the sample variance of a supplied set of
+// values. The syntax of the function is:
+//
+// VAR.S(number1,[number2],...)
+//
+func (fn *formulaFuncs) VARdotS(argsList *list.List) formulaArg {
+ return fn.vars("VAR.S", argsList)
+}
+
// VARPA function returns the Variance of a given set of values. The syntax of
// the function is:
//
diff --git a/calc_test.go b/calc_test.go
index 46f4f08..eb63130 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -878,6 +878,9 @@ func TestCalcCellValue(t *testing.T) {
"=MINA(MUNIT(2))": "0",
"=MINA(INT(1))": "1",
"=MINA(A1:B4,MUNIT(1),INT(0),1,E1:F2,\"\")": "0",
+ // PERCENTILE.EXC
+ "=PERCENTILE.EXC(A1:A4,0.2)": "0",
+ "=PERCENTILE.EXC(A1:A4,0.6)": "2",
// PERCENTILE.INC
"=PERCENTILE.INC(A1:A4,0.2)": "0.6",
// PERCENTILE
@@ -892,6 +895,10 @@ func TestCalcCellValue(t *testing.T) {
"=PERMUTATIONA(7,6)": "117649",
// QUARTILE
"=QUARTILE(A1:A4,2)": "1.5",
+ // QUARTILE.EXC
+ "=QUARTILE.EXC(A1:A4,1)": "0.25",
+ "=QUARTILE.EXC(A1:A4,2)": "1.5",
+ "=QUARTILE.EXC(A1:A4,3)": "2.75",
// QUARTILE.INC
"=QUARTILE.INC(A1:A4,0)": "0",
// RANK
@@ -933,6 +940,9 @@ func TestCalcCellValue(t *testing.T) {
"=VARP(1,3,5,0,C1,TRUE)": "3.2",
// VAR.P
"=VAR.P(A1:A5)": "1.25",
+ // VAR.S
+ "=VAR.S(1,3,5,0,C1)": "4.916666666666667",
+ "=VAR.S(1,3,5,0,C1,TRUE)": "4",
// VARPA
"=VARPA(1,3,5,0,C1)": "3.76",
"=VARPA(1,3,5,0,C1,TRUE)": "3.22222222222222",
@@ -2138,6 +2148,13 @@ func TestCalcCellValue(t *testing.T) {
// MINA
"=MINA()": "MINA requires at least 1 argument",
"=MINA(NA())": "#N/A",
+ // PERCENTILE.EXC
+ "=PERCENTILE.EXC()": "PERCENTILE.EXC requires 2 arguments",
+ "=PERCENTILE.EXC(A1:A4,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=PERCENTILE.EXC(A1:A4,-1)": "#NUM!",
+ "=PERCENTILE.EXC(A1:A4,0)": "#NUM!",
+ "=PERCENTILE.EXC(A1:A4,1)": "#NUM!",
+ "=PERCENTILE.EXC(NA(),0.5)": "#NUM!",
// PERCENTILE.INC
"=PERCENTILE.INC()": "PERCENTILE.INC requires 2 arguments",
// PERCENTILE
@@ -2161,6 +2178,11 @@ func TestCalcCellValue(t *testing.T) {
"=QUARTILE(A1:A4,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
"=QUARTILE(A1:A4,-1)": "#NUM!",
"=QUARTILE(A1:A4,5)": "#NUM!",
+ // QUARTILE.EXC
+ "=QUARTILE.EXC()": "QUARTILE.EXC requires 2 arguments",
+ "=QUARTILE.EXC(A1:A4,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=QUARTILE.EXC(A1:A4,0)": "#NUM!",
+ "=QUARTILE.EXC(A1:A4,4)": "#NUM!",
// QUARTILE.INC
"=QUARTILE.INC()": "QUARTILE.INC requires 2 arguments",
// RANK
@@ -2211,6 +2233,8 @@ func TestCalcCellValue(t *testing.T) {
// VAR.P
"=VAR.P()": "VAR.P requires at least 1 argument",
"=VAR.P(\"\")": "#DIV/0!",
+ // VAR.S
+ "=VAR.S()": "VAR.S requires at least 1 argument",
// VARPA
"=VARPA()": "VARPA requires at least 1 argument",
// WEIBULL
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 13:01:42 +0000