[JavaScript] Negative rounding does not match Excel

Introduction

Background and overview

--Requirements --Use JavaScript to round off to n decimal places --Match the calculation result with Excel

You cannot specify n decimal places in the Math object of JavaScript. I decided to create a function because I couldn't use the JavaScript library.

At this time, ** the calculation of negative numbers caused a difference between the results of JavaScript and Excel. ** ** We will share our findings on how to resolve this issue.

Prerequisite version

• ECMA Script 2015

problem

First of all, I tried this coding. A function that takes a number and the number of rounded digits and returns the rounded result.

JavaScript

function myRound(number, pricision) {
    var _pow = Math.pow(10, pricision);
    return Math.round(number * _pow) / _pow;
};

Positive numbers worked as expected, but negative numbers gave the following calculation results:

It is different from the calculation in Excel and cannot meet the requirements.

Solution and knowledge gained

The specification for rounding negative numbers differs between JavaScript (Math) and Excel. MDN - Math.round()

In other words, the "direction" that rounds 0.5 is the difference between the minus direction and the zero direction.

--Excel rounds in the minus direction, so -5.5 → -6 --Since JavaScript rounds to zero, -5.5 → -5

That's why.

When I modified the code as follows, the calculation result was the same as Excel.

JavaScript

function myRound(number, pricision) {
    var _sign = (number < 0) ? -1 : 1;
    var _pow = Math.pow(10, pricision);
    return Math.round((number * _sign) * _pow) / _pow * _sign;
};     

The same principle applies to rounding down and rounding up negative numbers, and the results differ between JavaScript and Excel functions. MDN - Math.floor() MDN - Math.ceil()

Also in Java, the direction of negative rounding is reversed between the Math class and the BigDecimal class. Be careful about rounding down and rounding negative Java numbers!

So, ** Be careful about the "direction" when rounding negative numbers! ** **

Articles that I used as a reference

Overload JavaScript Math.round and round to the nearest X digits