Maxima: Round like in Excel

Question

Is there a function which rounds numbers (even decimal numbers) like round() in Excel?

Example

Round 1,45 to one decimal: 1,5

Round 2,45 to one decimal: 2,5

There is a similar question but they use a different algorithm.

Answer 1

OK, here's an attempt to reimplement Excel =ROUND function in Maxima. Some notes. (1) Values are rounded to 15 significant digits before applying the user's rounding. This is an attempt to work around problems caused by inexact representation of decimals as floating point numbers. (2) I've implemented excel_round and integer_log10 as so-called simplifying functions. That means that the calculation isn't carried out until the arguments are something that can be evaluated (in this case, when the arguments are numbers). (3) I didn't check to see what Excel =ROUND does with negative numbers -- does it round 5 upward (i.e., towards zero in this case), or away from zero? I dunno.

I've posted this solution as the little package excel_round.mac on Github. See: https://github.com/maxima-project-on-github/maxima-packages and navigate to robert-dodier/excel_round. In the interest of completeness, I've pasted the code here as well.

Here are a few examples.

(%i1) excel_round (1.15, 1);
(%o1)                                 1.2
(%i2) excel_round (1.25, 1);
(%o2)                                 1.3
(%i3) excel_round (12.455, 2);
(%o3)                                12.46
(%i4) excel_round (x, 2);
(%o4)                          excel_round(x, 2)
(%i5) ev (%, x = 9.865);
(%o5)                                9.87

Here is the code. This is the content of excel_round.mac.

/* excel_round -- round to specified number of decimal places,
 * rounding termminal 5 upwards, as in MS Excel, apparently.
 * Inspired by: https://stackoverflow.com/q/62533742/871096
 *
 * copyright 2020 by Robert Dodier
 * I release this work under terms of the GNU General Public License.
 */

matchdeclare (xx, numberp);
matchdeclare (nn, integerp);
tellsimpafter (excel_round (xx, nn), excel_round_numerical (xx, nn));

matchdeclare (xx, lambda ([e], block ([v: ev (e, numer)], numberp(v))));
tellsimpafter (excel_round (xx, nn), excel_round_numerical (ev (xx, numer), nn));

excel_round_numerical (x, n) :=
  block ([r, r1, r2, l],
         /* rationalize returns exact rational equivalent of float */
         r: rationalize (x),
         /* First round to 15 significant decimal places.
          * This is a heuristic to recover what a user "meant"
          * to type in, since many decimal numbers are not
          * exactly representable as floats.
          */
         l: integer_log10 (abs (r)),
         r1: round (r*10^(15 - l)),
         /* Now begin rounding to n places. */
         r2: r1/10^((15 - l) - n),
         /* If terminal digit is 5, then r2 is integer + 1/2.
          * If that's the case, round upwards and rescale,
          * otherwise, terminal digit is something other than 5,
          * round to nearest integer and rescale.
          */
         if equal (r2 - floor(r2), 1/2)
           then ceiling(r2)/10.0^n
           else round(r2)/10.0^n);

matchdeclare (xx, lambda ([e], numberp(e) and e > 0));
tellsimpafter (integer_log10 (xx), integer_log10_numerical (xx));

matchdeclare (xx, lambda ([e], block ([v: ev (e, numer)], numberp(v) and v > 0)));
tellsimpafter (integer_log10 (xx), integer_log10_numerical (ev (xx, numer)));

matchdeclare (xx, lambda ([e], not atom(e) and op(e) = "/" and numberp (denom (e)) and pow10p (denom (e))));
pow10p (e) := integerp(e) and  e > 1 and (e = 10 or pow10p (e/10));
tellsimpafter (integer_log10 (xx), integer_log10 (num (xx)) - integer_log10_numerical (denom (xx)));

integer_log10_numerical (x) :=
  if x >= 10
    then (for i from 0 do
              if x >= 10 then x:x/10 else return(i))
  elseif x < 1
    then (for i from 0 do
              if x < 1 then x:x*10 else return(-i))
  else 0;

Answer 2

The problem of rounding numbers is actually pretty subtle, but here is a simple approach which I think gives workable results. Here I define a new function myround which has the behavior described for Excel =ROUND. [1]

(%i4) myround (x, n) := round(x*10^n)/10.0^n;
                                                    n
                                         'round(x 10 )
(%o4)                   myround(x, n) := -------------
                                                 n
                                             10.0
(%i5) myround (2.15, 1);
(%o5)                                 2.2
(%i6) myround (2.149, 1);
(%o6)                                 2.1
(%i7) myround (-1.475, 2);
(%o7)                               - 1.48
(%i8) myround (21.5, -1);
(%o8)                                20.0
(%i9) myround (626.3, -3);
(%o9)                               1000.0
(%i10) myround (1.98, -1);
(%o10)                                0.0
(%i11) myround (-50.55, -2);
(%o11)                              - 100.0

[1] https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c