Search code examples
javamathnumbersroundingbigdecimal

How to implement Excel MROUND() function in Java using BigDecimal instead of double

How can I round to a specific multiple in Java? In excel there is the mround function which allows for easy rounding to a specified multiple like so:

mRound(variable,multiple)

e.g. This is how the Java function should work for given 'Number' and 'Factor'. To get more test data, just add below 'Number' and 'Factor' columns in MS Excel and in the next column use excel's built in MROUND function as =MROUND(Number, Factor)

enter image description here

I know how it can be done with double, but I need a solution with BigDecimal as I am working with financial data.

Solution with double -

double mRound(double value, double factor) {
    return Math.round(value / factor) * factor;
}

This is how excel MROUND function works - The MROUND Excel function rounds the number to the nearest multiple of the provided number. You must be thinking about how Excel knows whether to round up or round down. It depends on the result of the division.

For example 16 / 3 = 5.33333, here decimal value is less than 0.5 so result of nearest multiple value of number 3 is 5, so 3 * 5 = 15. Here, Excel rounds down the value if the decimal value is less than 0.5.

Similarly, look at this equation 20 / 3 = 6.66667. In this equation, the decimal value is greater than 0.5, so Excel rounds up the result to 7, so 3 * 7 = 21.

For more details on how MROUND works, refer - https://www.wallstreetmojo.com/mround-in-excel/

Solution

  • The BigDecimal class offers all needed methods to do the same as Math.round(value / factor) * factor; - rounding is a bit tricky, since BigDecimal#round is using MathContext which requires a precision: the total number of digits, not the number of digits of the fractional part. But BigDecimal#setScale is a replacement for Math#round.

    Assuming that both values and factor are already BigDecimal, we would write your method as:

    static BigDecimal mRound(BigDecimal value, BigDecimal factor) {
    return 
        value
        .divide(factor)
        .setScale(0, RoundingMode.HALF_UP)
        .multiply(factor);
}

    I believe the divde() and multiply() methods do not need more explanation, for details refer to the javadoc of BigDecimal.

    The setScale() methods is used to round the number. As documented: "the scale is the number of digits to the right of the decimal point."
    We used:

    • scale: 0 since we want no decimal digits;
    • roundingMode: HALF_UP since we want 0.5 or greater rounded up
      again, see documentation for the different modes.

    To remove the trailing zeros from the result, use the stripTrailingZeros() method after multiplying:

    return 
    ...
    .multiply(factor)
    .stripTrailingZeros();

    Please be aware that using new BigDecimal(double) can cause problems since double is not an exact representation - use new BigDecimal(String) instead!
    For example, new BigDecimal(1.2) results in 1.1999999999999999555910790149937383830547332763671875.

    Testing

    List<List<BigDecimal>> data = """
    523.235  0.05
    523.265  0.05
    523.205  0.05
    523.735  0.05
    523.785  0.05
    523.75   0.05
    523.705  0.05
    15       8
    17       5
    523.235  0.01
    523.265  0.01
    523.205  0.01
    523.735  0.01
    523.785  0.01
    523.75   0.01
    523.705  0.01
    """
.lines()
.map(line -> Arrays.stream(line.split("\\h+")).map(BigDecimal::new).toList())
.toList();
for (var test : data) {
    var value = test.get(0);
    var factor = test.get(1);
    var result = mRound(value, factor);
    System.out.printf("%10s  %5s  %10s%n", value, factor, result);
}

    Result

       523.235   0.05      523.25
   523.265   0.05      523.25
   523.205   0.05      523.20
   523.735   0.05      523.75
   523.785   0.05      523.80
    523.75   0.05      523.75
   523.705   0.05      523.70
        15      8          16
        17      5          15
   523.235   0.01      523.24
   523.265   0.01      523.27
   523.205   0.01      523.21
   523.735   0.01      523.74
   523.785   0.01      523.79
    523.75   0.01      523.75
   523.705   0.01      523.71

    Note the result for 523.785 0.01 does not match the given input image - I wonder if that is a bug in Excel (is it using double?) :-/