Javascript Question posted in
A very good W3school tutorial can be found here.

Excel's Round Function to JavaScript does not providing consistent results with compared to Excel

  • While translating the ROUND function from Excel to JavaScript, it’s noted that the differences in how each language handles floating-point arithmetic. In JavaScript, the .toFixed() method is not giving the same result as Excel due to the way JavaScript handles binary floating-point numbers.

  • .toFixed() method converts a number into a string, rounding to a specified number of decimal places, but it can be affected by the floating-point representation of numbers in JavaScript. For instance, the number 179.725 may be represented internally as slightly less than the actual value, resulting in toFixed(2) returning 179.72 instead of 179.73.

  • So that I have used to calculate the rounding result by adding 0.001 based on the last position of the provided number and it works properly as same as Excel for me. But the case is when the provided number has a value of 0.499, while rounding it adds 0.001 to it and results in a wrong value as compared to the Excel.

numValue = 0.3049945849;
decimalValue = 2;
sign = numValue >= 0 ? 1 : -1;
roundValue = (Math.round((numValue * Math.pow(10, decimalValue)) + (sign * 0.001)) / Math.pow(10, decimalValue)).toFixed(decimalValue);

I need a way to replicated the ROUND formula used is Excel in JavaScript

Tags:

2

Answers


  1. 0 
    function roundNumber(numValue, decimalValue) {
 
    const factor = Math.pow(10, decimalValue);
    
  
    const tempNumber = Math.round((numValue * factor) + Number.EPSILON);

  
    const roundedValue = tempNumber / factor;

    return roundedValue.toFixed(decimalValue);
}


let numValue = 0.3049945849;
let decimalValue = 2;


let roundValue = roundNumber(numValue, decimalValue);

console.log(roundValue); // "0.30"
    Login or Signup to reply.
  2. 0

    To accurately replicate Excel’s ROUND function in JavaScript, you can use a straightforward custom function that avoids the pitfalls of JavaScript’s floating-point arithmetic. Here’s a concise version of such a function:

    function roundExcel(number, decimals) {
    const factor = Math.pow(10, decimals);
    return (Math.round(number * factor) / factor).toFixed(decimals);
}

    Usage:

    console.log(roundExcel(179.725, 2));  // Outputs: 179.73
console.log(roundExcel(0.499, 0));    // Outputs: 0
console.log(roundExcel(0.3049945849, 2));  // Outputs: 0.30

    This function scales the number, rounds it, and then scales it back, using .toFixed() for formatting, ensuring results consistent with Excel’s rounding.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search