1package functions23import (4 "math"56 "src.rest/cat/012/internal/value"7)89func init() {10 define(11 &FuncDef{Name: "AVERAGEIF", Args: "criteria_range, criterion, [average_range]", Desc: "Average of the cells that meet a condition", Min: 2, Max: 3,12 eval: averageIf, format: inheritFrom(2)},13 &FuncDef{Name: "AVERAGEIFS", Args: "average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]", Desc: "Average of the cells that meet every condition", Min: 3, Max: -1, step: 2,14 eval: averageIfs, format: inheritFrom(0)},15 &FuncDef{Name: "COUNTIF", Args: "range, criterion", Desc: "Count of the cells that meet a condition", Min: 2, Max: 2,16 eval: countIfs},17 &FuncDef{Name: "COUNTIFS", Args: "criteria_range1, criterion1, [criteria_range2, criterion2, ...]", Desc: "Count of the cells that meet every condition", Min: 2, Max: -1, step: 2,18 eval: countIfs},19 &FuncDef{Name: "COUNTBLANK", Args: "range", Desc: "Count of empty cells, including empty text", Min: 1, Max: 1,20 eval: countBlank},21 &FuncDef{Name: "MEDIAN", Args: "value1, [value2, ...]", Desc: "Middle value of numbers", Min: 1, Max: -1,22 eval: withSorted(true, medianOf), format: inherit},23 &FuncDef{Name: "MODE", Args: "value1, [value2, ...]", Desc: "Most common number (the first, on a tie)", Min: 1, Max: -1,24 eval: withSorted(true, modeOf), format: inherit},25 &FuncDef{Name: "STDEV", Args: "value1, [value2, ...]", Desc: "Standard deviation of a sample", Min: 1, Max: -1,26 eval: withNums(func(x []float64) Value { return spread(x, 1, true) })},27 &FuncDef{Name: "STDEVP", Args: "value1, [value2, ...]", Desc: "Standard deviation of a whole population", Min: 1, Max: -1,28 eval: withNums(func(x []float64) Value { return spread(x, 0, true) })},29 &FuncDef{Name: "VAR", Args: "value1, [value2, ...]", Desc: "Variance of a sample", Min: 1, Max: -1,30 eval: withNums(func(x []float64) Value { return spread(x, 1, false) })},31 &FuncDef{Name: "VARP", Args: "value1, [value2, ...]", Desc: "Variance of a whole population", Min: 1, Max: -1,32 eval: withNums(func(x []float64) Value { return spread(x, 0, false) })},33 &FuncDef{Name: "LARGE", Args: "data, n", Desc: "The nth largest number", Min: 2, Max: 2,34 eval: withSorted(false, nthOf(true)), format: inheritFrom(0)},35 &FuncDef{Name: "SMALL", Args: "data, n", Desc: "The nth smallest number", Min: 2, Max: 2,36 eval: withSorted(false, nthOf(false)), format: inheritFrom(0)},37 &FuncDef{Name: "RANK", Args: "value, data, [is_ascending]", Desc: "Rank of a number among others, largest first by default", Min: 2, Max: 3,38 eval: rank},39 &FuncDef{Name: "RANK.EQ", Args: "value, data, [is_ascending]", Desc: "Rank of a number among others, largest first by default (RANK)", Min: 2, Max: 3,40 eval: rank},41 &FuncDef{Name: "MODE.SNGL", Args: "value1, [value2, ...]", Desc: "Most common number, the first on a tie (MODE)", Min: 1, Max: -1,42 eval: withSorted(true, modeOf), format: inherit},43 &FuncDef{Name: "PERCENTILE", Args: "data, percentile", Desc: "The value at a percentile from 0 to 1, interpolated", Min: 2, Max: 2,44 eval: withSorted(false, percentileOf(false, false)), format: inheritFrom(0)},45 &FuncDef{Name: "PERCENTILE.INC", Args: "data, percentile", Desc: "The value at a percentile from 0 to 1, interpolated (PERCENTILE)", Min: 2, Max: 2,46 eval: withSorted(false, percentileOf(false, false)), format: inheritFrom(0)},47 &FuncDef{Name: "PERCENTILE.EXC", Args: "data, percentile", Desc: "The value at a percentile strictly between 0 and 1, interpolated", Min: 2, Max: 2,48 eval: withSorted(false, percentileOf(true, false)), format: inheritFrom(0)},49 &FuncDef{Name: "QUARTILE", Args: "data, quartile", Desc: "The minimum (0), a quartile (1 to 3) or the maximum (4)", Min: 2, Max: 2,50 eval: withSorted(false, percentileOf(false, true)), format: inheritFrom(0)},51 &FuncDef{Name: "QUARTILE.INC", Args: "data, quartile", Desc: "The minimum (0), a quartile (1 to 3) or the maximum (4) (QUARTILE)", Min: 2, Max: 2,52 eval: withSorted(false, percentileOf(false, true)), format: inheritFrom(0)},53 &FuncDef{Name: "QUARTILE.EXC", Args: "data, quartile", Desc: "A quartile from 1 to 3, between the numbers rather than at them", Min: 2, Max: 2,54 eval: withSorted(false, percentileOf(true, true)), format: inheritFrom(0)},55 )56}5758// Evaluators for the table above, in its order.5960// rank counts the numbers of data above and below the value as it reads61// them, holding none, so a linked source's column of any size streams.62func rank(args []Node, get lookup) Value {63 v, err := numArg(args[0], get)64 if err != nil {65 return *err66 }67 above, below, found := 0, 0, false68 err = each(args[1:2], get, func(d Value, direct bool) *Value {69 switch {70 case d.Kind == value.Error:71 return errOf(d)72 case d.Kind == value.Empty, d.Kind != value.Number && !direct:73 return nil74 }75 f, err := toNum(d)76 switch {77 case err != nil:78 return err79 case f == v:80 found = true81 case f > v:82 above++83 default:84 below++85 }86 return nil87 })88 if err != nil {89 return *err90 }91 asc, err := boolArg(args, 2, false, get)92 if err != nil {93 return *err94 }95 if !found {96 return value.ErrNA97 }98 if asc {99 return num(float64(below + 1))100 }101 return num(float64(above + 1))102}103104// withNums passes the numbers of all arguments, with aggregate105// semantics, to f.106func withNums(f func([]float64) Value) func([]Node, lookup) Value {107 return func(args []Node, get lookup) Value {108 x, err := nums(args, get)109 if err != nil {110 return *err111 }112 return f(x)113 }114}115116// spread computes variance (or its root) with ddof 1 for samples and 0117// for populations.118func spread(x []float64, ddof int, root bool) Value {119 n := len(x)120 if n-ddof <= 0 {121 return value.ErrDiv0122 }123 mean := 0.0124 for _, v := range x {125 mean += v126 }127 mean /= float64(n)128 ss := 0.0129 for _, v := range x {130 ss += (v - mean) * (v - mean)131 }132 v := ss / float64(n-ddof)133 if root {134 v = math.Sqrt(v)135 }136 return num(v)137}138139func cmpFloat(a, b float64) int {140 switch {141 case a < b:142 return -1143 case a > b:144 return 1145 }146 return 0147}