diff options
| -rw-r--r-- | calc.go | 260 | ||||
| -rw-r--r-- | calc_test.go | 81 |
2 files changed, 339 insertions, 2 deletions
@@ -2078,6 +2078,141 @@ func (fn *formulaFuncs) ROMAN(argsList *list.List) (result string, err error) { return } +type roundMode byte + +const ( + closest roundMode = iota + down + up +) + +// round rounds a supplied number up or down. +func (fn *formulaFuncs) round(number, digits float64, mode roundMode) float64 { + significance := 1.0 + if digits > 0 { + significance = math.Pow(1/10.0, digits) + } else { + significance = math.Pow(10.0, -digits) + } + val, res := math.Modf(number / significance) + switch mode { + case closest: + const eps = 0.499999999 + if res >= eps { + val++ + } else if res <= -eps { + val-- + } + case down: + case up: + if res > 0 { + val++ + } else if res < 0 { + val-- + } + } + return val * significance +} + +// ROUND function rounds a supplied number up or down, to a specified number +// of decimal places. The syntax of the function is: +// +// ROUND(number,num_digits) +// +func (fn *formulaFuncs) ROUND(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUND requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, closest)) + return +} + +// ROUNDDOWN function rounds a supplied number down towards zero, to a +// specified number of decimal places. The syntax of the function is: +// +// ROUNDDOWN(number,num_digits) +// +func (fn *formulaFuncs) ROUNDDOWN(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUNDDOWN requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, down)) + return +} + +// ROUNDUP function rounds a supplied number up, away from zero, to a +// specified number of decimal places. The syntax of the function is: +// +// ROUNDUP(number,num_digits) +// +func (fn *formulaFuncs) ROUNDUP(argsList *list.List) (result string, err error) { + if argsList.Len() != 2 { + err = errors.New("ROUNDUP requires 2 numeric arguments") + return + } + var number, digits float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", fn.round(number, digits, up)) + return +} + +// SEC function calculates the secant of a given angle. The syntax of the +// function is: +// +// SEC(number) +// +func (fn *formulaFuncs) SEC(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SEC requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Cos(number)) + return +} + +// SECH function calculates the hyperbolic secant (sech) of a supplied angle. +// The syntax of the function is: +// +// SECH(number) +// +func (fn *formulaFuncs) SECH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SECH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", 1/math.Cosh(number)) + return +} + // SIGN function returns the arithmetic sign (+1, -1 or 0) of a supplied // number. I.e. if the number is positive, the Sign function returns +1, if // the number is negative, the function returns -1 and if the number is 0 @@ -2106,6 +2241,42 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) { return } +// SIN function calculates the sine of a given angle. The syntax of the +// function is: +// +// SIN(number) +// +func (fn *formulaFuncs) SIN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SIN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sin(number)) + return +} + +// SINH function calculates the hyperbolic sine (sinh) of a supplied number. +// The syntax of the function is: +// +// SINH(number) +// +func (fn *formulaFuncs) SINH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SINH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sinh(number)) + return +} + // SQRT function calculates the positive square root of a supplied number. The // syntax of the function is: // @@ -2133,6 +2304,24 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) { return } +// SQRTPI function returns the square root of a supplied number multiplied by +// the mathematical constant, π. The syntax of the function is: +// +// SQRTPI(number) +// +func (fn *formulaFuncs) SQRTPI(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("SQRTPI requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Sqrt(number*math.Pi)) + return +} + // SUM function adds together a supplied set of numbers and returns the sum of // these values. The syntax of the function is: // @@ -2153,3 +2342,74 @@ func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) { result = fmt.Sprintf("%g", sum) return } + +// TAN function calculates the tangent of a given angle. The syntax of the +// function is: +// +// TAN(number) +// +func (fn *formulaFuncs) TAN(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("TAN requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Tan(number)) + return +} + +// TANH function calculates the hyperbolic tangent (tanh) of a supplied +// number. The syntax of the function is: +// +// TANH(number) +// +func (fn *formulaFuncs) TANH(argsList *list.List) (result string, err error) { + if argsList.Len() != 1 { + err = errors.New("TANH requires 1 numeric argument") + return + } + var number float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + result = fmt.Sprintf("%g", math.Tanh(number)) + return +} + +// TRUNC function truncates a supplied number to a specified number of decimal +// places. The syntax of the function is: +// +// TRUNC(number,[number_digits]) +// +func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) { + if argsList.Len() == 0 { + err = errors.New("TRUNC requires at least 1 argument") + return + } + var number, digits, adjust, rtrim float64 + if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil { + return + } + if argsList.Len() > 1 { + if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil { + return + } + digits = math.Floor(digits) + } + adjust = math.Pow(10, digits) + x := int((math.Abs(number) - math.Abs(float64(int(number)))) * adjust) + if x != 0 { + if rtrim, err = strconv.ParseFloat(strings.TrimRight(strconv.Itoa(x), "0"), 64); err != nil { + return + } + } + if (digits > 0) && (rtrim < adjust/10) { + result = fmt.Sprintf("%g", number) + return + } + result = fmt.Sprintf("%g", float64(int(number*adjust))/adjust) + return +} diff --git a/calc_test.go b/calc_test.go index 2b35e48..6225d50 100644 --- a/calc_test.go +++ b/calc_test.go @@ -266,14 +266,55 @@ func TestCalcCellValue(t *testing.T) { "=ROMAN(1999,2)": "MXMIX", "=ROMAN(1999,3)": "MVMIV", "=ROMAN(1999,4)": "MIM", + // ROUND + "=ROUND(100.319,1)": "100.30000000000001", + "=ROUND(5.28,1)": "5.300000000000001", + "=ROUND(5.9999,3)": "6.000000000000002", + "=ROUND(99.5,0)": "100", + "=ROUND(-6.3,0)": "-6", + "=ROUND(-100.5,0)": "-101", + "=ROUND(-22.45,1)": "-22.5", + "=ROUND(999,-1)": "1000", + "=ROUND(991,-1)": "990", + // ROUNDDOWN + "=ROUNDDOWN(99.999,1)": "99.9", + "=ROUNDDOWN(99.999,2)": "99.99000000000002", + "=ROUNDDOWN(99.999,0)": "99", + "=ROUNDDOWN(99.999,-1)": "90", + "=ROUNDDOWN(-99.999,2)": "-99.99000000000002", + "=ROUNDDOWN(-99.999,-1)": "-90", + // ROUNDUP + "=ROUNDUP(11.111,1)": "11.200000000000001", + "=ROUNDUP(11.111,2)": "11.120000000000003", + "=ROUNDUP(11.111,0)": "12", + "=ROUNDUP(11.111,-1)": "20", + "=ROUNDUP(-11.111,2)": "-11.120000000000003", + "=ROUNDUP(-11.111,-1)": "-20", + // SEC + "=_xlfn.SEC(-3.14159265358979)": "-1", + "=_xlfn.SEC(0)": "1", + // SECH + "=_xlfn.SECH(-3.14159265358979)": "0.0862667383340547", + "=_xlfn.SECH(0)": "1", // SIGN "=SIGN(9.5)": "1", "=SIGN(-9.5)": "-1", "=SIGN(0)": "0", "=SIGN(0.00000001)": "1", "=SIGN(6-7)": "-1", + // SIN + "=SIN(0.785398163)": "0.7071067809055092", + // SINH + "=SINH(0)": "0", + "=SINH(0.5)": "0.5210953054937474", + "=SINH(-2)": "-3.626860407847019", // SQRT "=SQRT(4)": "2", + // SQRTPI + "=SQRTPI(5)": "3.963327297606011", + "=SQRTPI(0.2)": "0.7926654595212022", + "=SQRTPI(100)": "17.72453850905516", + "=SQRTPI(0)": "0", // SUM "=SUM(1,2)": "3", "=SUM(1,2+3)": "6", @@ -288,6 +329,20 @@ func TestCalcCellValue(t *testing.T) { "=((3+5*2)+3)/5+(-6)/4*2+3": "3.2", "=1+SUM(SUM(1,2*3),4)*-4/2+5+(4+2)*3": "2", "=1+SUM(SUM(1,2*3),4)*4/3+5+(4+2)*3": "38.666666666666664", + // TAN + "=TAN(1.047197551)": "1.732050806782486", + "=TAN(0)": "0", + // TANH + "=TANH(0)": "0", + "=TANH(0.5)": "0.46211715726000974", + "=TANH(-2)": "-0.9640275800758169", + // TRUNC + "=TRUNC(99.999,1)": "99.9", + "=TRUNC(99.999,2)": "99.99", + "=TRUNC(99.999)": "99", + "=TRUNC(99.999,-1)": "90", + "=TRUNC(-99.999,2)": "-99.99", + "=TRUNC(-99.999,-1)": "-90", } for formula, expected := range mathCalc { f := prepareData() @@ -431,11 +486,33 @@ func TestCalcCellValue(t *testing.T) { // ROMAN "=ROMAN()": "ROMAN requires at least 1 argument", "=ROMAN(1,2,3)": "ROMAN allows at most 2 arguments", + // ROUND + "=ROUND()": "ROUND requires 2 numeric arguments", + // ROUNDDOWN + "=ROUNDDOWN()": "ROUNDDOWN requires 2 numeric arguments", + // ROUNDUP + "=ROUNDUP()": "ROUNDUP requires 2 numeric arguments", + // SEC + "=_xlfn.SEC()": "SEC requires 1 numeric argument", + // _xlfn.SECH + "=_xlfn.SECH()": "SECH requires 1 numeric argument", // SIGN "=SIGN()": "SIGN requires 1 numeric argument", + // SIN + "=SIN()": "SIN requires 1 numeric argument", + // SINH + "=SINH()": "SINH requires 1 numeric argument", // SQRT - "=SQRT(-1)": "#NUM!", - "=SQRT(1,2)": "SQRT requires 1 numeric argument", + "=SQRT()": "SQRT requires 1 numeric argument", + "=SQRT(-1)": "#NUM!", + // SQRTPI + "=SQRTPI()": "SQRTPI requires 1 numeric argument", + // TAN + "=TAN()": "TAN requires 1 numeric argument", + // TANH + "=TANH()": "TANH requires 1 numeric argument", + // TRUNC + "=TRUNC()": "TRUNC requires at least 1 argument", } for formula, expected := range mathCalcError { f := prepareData() |