1package functions23import (4 "math"5 "math/rand/v2"67 "src.rest/cat/012/internal/numfmt"8 "src.rest/cat/012/internal/value"9)1011func init() {12 define(13 &FuncDef{Name: "SUMIF", Args: "range, criterion, [sum_range]", Desc: "Sum of the cells that meet a condition", Min: 2, Max: 3,14 eval: sumIf, format: inheritFrom(2)},15 &FuncDef{Name: "SUMIFS", Args: "sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]", Desc: "Sum of the cells that meet every condition", Min: 3, Max: -1, step: 2,16 eval: sumIfs, format: inheritFrom(0)},17 &FuncDef{Name: "SUMPRODUCT", Args: "array1, [array2, ...]", Desc: "Sum of the products of matching entries, TRUE counting as 1", Min: 1, Max: -1,18 eval: sumProduct},19 &FuncDef{Name: "PRODUCT", Args: "factor1, [factor2, ...]", Desc: "Product of numbers", Min: 1, Max: -1,20 eval: aggregate(func(s Agg) Value {21 if s.nums == 0 {22 return num(0)23 }24 return num(s.prod)25 })},26 &FuncDef{Name: "POWER", Args: "base, exponent", Desc: "A number raised to a power, odd roots of negatives included", Min: 2, Max: 2,27 eval: numeric(func(x []float64) Value { return powerOf(x[0], x[1]) })},28 &FuncDef{Name: "ROUNDUP", Args: "value, [places]", Desc: "Round away from zero", Min: 1, Max: 2,29 eval: rounder(numfmt.Up), format: inheritFrom(0)},30 &FuncDef{Name: "ROUNDDOWN", Args: "value, [places]", Desc: "Round toward zero", Min: 1, Max: 2,31 eval: rounder(numfmt.Down), format: inheritFrom(0)},32 &FuncDef{Name: "TRUNC", Args: "value, [places]", Desc: "Drop decimals past a number of places", Min: 1, Max: 2,33 eval: rounder(numfmt.Down), format: inheritFrom(0)},34 &FuncDef{Name: "CEILING", Args: "value, [factor]", Desc: "Round up to a multiple of factor", Min: 1, Max: 2,35 eval: toMultiple(math.Ceil), format: inheritFrom(0)},36 &FuncDef{Name: "FLOOR", Args: "value, [factor]", Desc: "Round down to a multiple of factor", Min: 1, Max: 2,37 eval: toMultiple(math.Floor), format: inheritFrom(0)},38 &FuncDef{Name: "SIGN", Args: "value", Desc: "1, 0 or -1 by the sign of a number", Min: 1, Max: 1,39 eval: numeric(func(x []float64) Value {40 switch {41 case x[0] > 0:42 return num(1)43 case x[0] < 0:44 return num(-1)45 }46 return num(0)47 })},48 &FuncDef{Name: "EXP", Args: "exponent", Desc: "e raised to a power", Min: 1, Max: 1, eval: math1(math.Exp)},49 &FuncDef{Name: "LN", Args: "value", Desc: "Natural logarithm", Min: 1, Max: 1,50 eval: numeric(func(x []float64) Value {51 if x[0] <= 0 {52 return value.ErrNum53 }54 return num(math.Log(x[0]))55 })},56 &FuncDef{Name: "LOG", Args: "value, [base]", Desc: "Logarithm, base 10 by default", Min: 1, Max: 2,57 eval: func(args []Node, get lookup) Value {58 x, err := numArg(args[0], get)59 if err != nil {60 return *err61 }62 base, err := optNum(args, 1, 10, get)63 if err != nil {64 return *err65 }66 switch {67 case x <= 0 || base <= 0:68 return value.ErrNum69 case base == 1:70 return value.ErrDiv071 }72 return num(math.Log(x) / math.Log(base))73 }},74 &FuncDef{Name: "LOG10", Args: "value", Desc: "Base-10 logarithm", Min: 1, Max: 1,75 eval: numeric(func(x []float64) Value {76 if x[0] <= 0 {77 return value.ErrNum78 }79 return num(math.Log10(x[0]))80 })},81 &FuncDef{Name: "QUOTIENT", Args: "dividend, divisor", Desc: "Integer part of a division", Min: 2, Max: 2,82 eval: numeric(func(x []float64) Value {83 if x[1] == 0 {84 return value.ErrDiv085 }86 return num(math.Trunc(x[0] / x[1]))87 })},88 &FuncDef{Name: "RAND", Desc: "A random number from 0 up to 1, new on every change", Max: 0, Volatile: true,89 eval: func([]Node, lookup) Value { return num(rand.Float64()) }},90 &FuncDef{Name: "RANDBETWEEN", Args: "low, high", Desc: "A random integer between two values, new on every change", Min: 2, Max: 2, Volatile: true,91 eval: numeric(func(x []float64) Value {92 lo, hi := math.Ceil(x[0]), math.Floor(x[1])93 if lo > hi {94 return value.ErrNum95 }96 return num(lo + math.Floor(rand.Float64()*(hi-lo+1)))97 })},98 )99}100101func powerOf(b, e float64) Value {102 if b == 0 && e < 0 {103 return value.ErrDiv0104 }105 return num(power(b, e))106}107108// power is b raised to e, as POWER and ^ compute it. A negative base109// has a real odd root, as in Sheets: (-8)^(1/3) is -2, where math.Pow110// gives NaN (#NUM!). Other fractional powers of a negative stay NaN.111func power(b, e float64) float64 {112 if b >= 0 || e == math.Trunc(e) {113 return math.Pow(b, e)114 }115 n := math.Round(1 / e) // e is 1/n, or near it: 1/3 isn't exact116 if math.Abs(1/e-n) > 1e-9*math.Abs(n) || math.Mod(n, 2) == 0 {117 return math.NaN()118 }119 return -math.Pow(-b, e)120}121122// toMultiple builds CEILING and FLOOR with Sheets semantics: the factor123// defaults to 1, a zero factor gives 0, and a positive value with a124// negative factor is #NUM!.125func toMultiple(round func(float64) float64) func([]Node, lookup) Value {126 return func(args []Node, get lookup) Value {127 x, err := numArg(args[0], get)128 if err != nil {129 return *err130 }131 f, err := optNum(args, 1, 1, get)132 if err != nil {133 return *err134 }135 switch {136 case f == 0 || x == 0:137 return num(0)138 case x > 0 && f < 0:139 return value.ErrNum140 }141 q := x / f142 // Snap quotients like 2.0000000000000004 to the integer they mean.143 if r := math.Round(q); math.Abs(q-r) < 1e-12*math.Max(1, math.Abs(q)) {144 q = r145 }146 // With both negative the quotient is positive, so CEILING moves147 // the result away from zero and FLOOR toward it.148 return num(round(q) * f)149 }150}