diff options
| -rw-r--r-- | calc.go | 69 | ||||
| -rw-r--r-- | calc_test.go | 12 |
2 files changed, 81 insertions, 0 deletions
@@ -20,6 +20,7 @@ import ( "math/rand" "reflect" "regexp" + "sort" "strconv" "strings" "time" @@ -2839,6 +2840,52 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) { // Statistical functions +// MEDIAN function returns the statistical median (the middle value) of a list +// of supplied numbers. The syntax of the function is: +// +// MEDIAN(number1,[number2],...) +// +func (fn *formulaFuncs) MEDIAN(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("MEDIAN requires at least 1 argument") + return + } + values := []float64{} + var median, digits float64 + for token := argsList.Front(); token != nil; token = token.Next() { + arg := token.Value.(formulaArg) + switch arg.Type { + case ArgString: + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil { + err = errors.New(formulaErrorVALUE) + return + } + values = append(values, digits) + case ArgMatrix: + for _, row := range arg.Matrix { + for _, value := range row { + if value.String == "" { + continue + } + if digits, err = strconv.ParseFloat(value.String, 64); err != nil { + err = errors.New(formulaErrorVALUE) + return + } + values = append(values, digits) + } + } + } + } + sort.Float64s(values) + if len(values)%2 == 0 { + median = (values[len(values)/2-1] + values[len(values)/2]) / 2 + } else { + median = values[len(values)/2] + } + result = fmt.Sprintf("%g", median) + return +} + // Information functions // ISBLANK function tests if a specified cell is blank (empty) and if so, @@ -2977,6 +3024,28 @@ func (fn *formulaFuncs) ISNONTEXT(argsList *list.List) (result string, err error return } +// ISNUMBER function function tests if a supplied value is a number. If so, +// the function returns TRUE; Otherwise it returns FALSE. The syntax of the +// function is: +// +// ISNUMBER(value) +// +func (fn *formulaFuncs) ISNUMBER(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("ISNUMBER requires 1 argument") + return + } + token := argsList.Front().Value.(formulaArg) + result = "FALSE" + if token.Type == ArgString && token.String != "" { + if _, err = strconv.Atoi(token.String); err == nil { + result = "TRUE" + } + err = nil + } + return +} + // ISODD function tests if a supplied number (or numeric expression) evaluates // to an odd number, and if so, returns TRUE; Otherwise, the function returns // FALSE. The syntax of the function is: diff --git a/calc_test.go b/calc_test.go index 4f2ca7b..213f77a 100644 --- a/calc_test.go +++ b/calc_test.go @@ -384,6 +384,10 @@ func TestCalcCellValue(t *testing.T) { "=TRUNC(99.999,-1)": "90", "=TRUNC(-99.999,2)": "-99.99", "=TRUNC(-99.999,-1)": "-90", + // Statistical functions + // MEDIAN + "=MEDIAN(A1:A5,12)": "2", + "=MEDIAN(A1:A5)": "1.5", // Information functions // ISBLANK "=ISBLANK(A1)": "FALSE", @@ -405,6 +409,9 @@ func TestCalcCellValue(t *testing.T) { "=ISNONTEXT(A5)": "TRUE", `=ISNONTEXT("Excelize")`: "FALSE", "=ISNONTEXT(NA())": "FALSE", + // ISNUMBER + "=ISNUMBER(A1)": "TRUE", + "=ISNUMBER(D1)": "FALSE", // ISODD "=ISODD(A1)": "TRUE", "=ISODD(A2)": "FALSE", @@ -685,6 +692,9 @@ func TestCalcCellValue(t *testing.T) { "=TRUNC()": "TRUNC requires at least 1 argument", `=TRUNC("X")`: "#VALUE!", `=TRUNC(1,"X")`: "#VALUE!", + // Statistical functions + // MEDIAN + "=MEDIAN()": "MEDIAN requires at least 1 argument", // Information functions // ISBLANK "=ISBLANK(A1,A2)": "ISBLANK requires 1 argument", @@ -698,6 +708,8 @@ func TestCalcCellValue(t *testing.T) { "=ISNA()": "ISNA requires 1 argument", // ISNONTEXT "=ISNONTEXT()": "ISNONTEXT requires 1 argument", + // ISNUMBER + "=ISNUMBER()": "ISNUMBER requires 1 argument", // ISODD "=ISODD()": "ISODD requires 1 argument", // NA |