javascript and excel rounding wierdness

Question

I have excel sheet that contains some calculations, i need to port this to javascript, but i have discovered a real headache as values do not match.

In short, numbers need to presented as ##0.0 (single decimal) but any attempt to reach same value shown in excel is a failure.

Test script:

var excelReal = [60.78,51.05,36.14,30.93,21.65,14.76,10.35,7.64,5.51,3.82,2.44];
var excelFormat = [60.8,51.0,36.1,30.9,21.6,14.8,10.4,7.6,5.5,3.8,2.4];

console.log('Formatted values from excel:');
printArray(excelReal);
console.log('Single decimal formated from excel:');
printArray(excelFormat);
console.log('Using toFixed');
var fixed =iterate(excelReal,function(i){return i.toFixed(1);}); 
printArray(fixed);
compareArray(excelFormat,fixed);
console.log('Using custom round');
var r = iterate(excelReal,function(i){return round(i,1);});
printArray(r);
compareArray(excelFormat,r);

function printArray(what) {
    var out = '';
    for(ix=0;ix < what.length;ix++) {
        out+=what[ix] + ' ';
    }
    console.log(out);
}
function compareArray(what,withWhat) {
    for(var ix=0;ix < what.length;ix++) {
        if (what[ix]!=withWhat[ix]) console.log(what[ix] + ' ' + withWhat[ix] + ' not matching,index: ' + ix);
    }
}
function iterate(input,f)
{
    var out = [];
    for(ix=0;ix<input.length;ix++) {
        out.push(f(input[ix]));
    }
    return out;
}
function round(value, decimals) {
    return Number(Math.round(value+'e'+decimals)+'e-'+decimals);
}

Output will be: Original values from excel:

60.78 51.05 36.14 30.93 21.65 14.76 10.35 7.64 5.51 3.82 2.44

Single decimal formated from excel:

60.8 51 36.1 30.9 21.6 14.8 10.4 7.6 5.5 3.8 2.4

Using toFixed

60.8 51.0 36.1 30.9 21.6 14.8 10.3 7.6 5.5 3.8 2.4

10.4 10.3 not matching,index: 6

Using custom round

60.8 51.1 36.1 30.9 21.7 14.8 10.4 7.6 5.5 3.8 2.4

51 51.1 not matching,index: 1

21.6 21.7 not matching,index: 4

Any ideas how to fix this ....

[Is floating point math broken?](https://stackoverflow.com/q/588004/62576) — Ken White, Sep 20 '17 at 02:39
Rounding `10.35` to `10.4` or `10.3` is likely an issue accross different programming languages: different languages have [different definitions of rounding](https://en.wikipedia.org/wiki/Rounding#Tie-breaking). Is this your actual question? — Sebastian Simon, Sep 20 '17 at 02:44
using that sample ends with same issue as fixed i.e. mismatch at index 6 — Karl, Sep 20 '17 at 02:44
See this: there’s a similar issue in [Python](https://stackoverflow.com/q/10825926/4642212). — Sebastian Simon, Sep 20 '17 at 02:47
Thank You, however i need to find a solution in JavaScript, so Python is not usable :) — Karl, Sep 20 '17 at 02:49
Looks like a dupe of [Gaussian/banker's rounding in JavaScript](https://stackoverflow.com/q/3108986/4642212). — Sebastian Simon, Sep 20 '17 at 02:53
Solution was found here: https://stackoverflow.com/questions/3108986/gaussian-bankers-rounding-in-javascript Thank You Xufox! — Karl, Sep 20 '17 at 03:00

Dev · Answer 1 · 2017-09-20T03:11:13.770

For me, it is working correctly in excel. Maybe the values you are giving are not exact numbers but formatted numbers.

Data    As per You in excel My Value after =ROUND(A2,1) and like that
60.78   60.8                      60.8
51.05   51                        51.1
36.14   36.1                      36.1
30.93   30.9                      30.9
21.65   21.6                      21.7
14.76   14.8                      14.8
10.35   10.4                      10.4
7.64    7.6                        7.6
5.51    5.5                        5.5
3.82    3.8                        3.8
2.44    2.4                        2.4

https://drive.google.com/file/d/0Bz49ZLVCmFjeQTI4UFNxMHBhTHc/view?usp=sharing

javascript and excel rounding wierdness

1 Answers1