Numbers Rounding #2824

New Issue

andrei-kav · 2022-11-11T21:30:34Z

andrei-kav commented

2022-11-11 21:30:34 +00:00

I know there are a lot of tricky moments with floating point numbers. I have bumped into one of them. In the excel file i have 15, 14, 16, 1.26, 1.36, but when i am reading it using sheetjs i am getting 14, 14, 16, 1.25, 1.36.
It there something that i can do to manage it?
Or as i understood from this thread (https://github.com/SheetJS/sheetjs/issues/1105) this is the issue of Excell and nothing has changed since that time.
Thank you in advance!

I know there are a lot of tricky moments with floating point numbers. I have bumped into one of them. In the excel file i have 15, 14, 16, 1.26, 1.36, but when i am reading it using sheetjs i am getting **14**, 14, 16, **1.25**, 1.36. It there something that i can do to manage it? Or as i understood from this thread (https://github.com/SheetJS/sheetjs/issues/1105) this is the issue of Excell and nothing has changed since that time. Thank you in advance!

import-rouding-error.xlsx

4.9 KiB

1.png

14 KiB

2.png

7.5 KiB

sheetjs commented

2022-11-11 21:47:01 +00:00

Thanks for reporting! This appears to be an error in the rounding logic, since the file has the correct value 0.145.

Thanks for reporting! This appears to be an error in the rounding logic, since the file has the correct value `0.145`.

andrei-kav commented

2022-11-12 13:11:19 +00:00

You are welcome! Are you going to fix it?

sheetjs commented

2022-11-21 10:07:02 +00:00

Thanks for reporting and apologies for the delay! The upcoming Thanksgiving holiday has definitely strained the schedule.

This is similar to #1105 .

Excel does not round in some cases. As a simple example, try setting A1=0.1+0.2 and A2=0.3, then format both as Fraction with up to 1 digit. That will render "2/7" in A1 and "1/3" in A2. When looking at the file in XLSX, the value in A1 is the exact IEEE754 value with the ULP (0.30000000000000004) while the value in A2 is the "rounded" value (0.3)

So clearly some part of the formatting process cares about the exact value.

Bisecting by editing an XLSX file in Vim, 0.14499999999999950 => 0.14 and 0.14499999999999951 => 0.15 (see attached)

Can you confirm you see the same results in Excel (try opening the attachment in Excel. A2 is 0.14 and B2 is 0.15)? Do you see the correct formatted values after the patch?

Thanks for reporting and apologies for the delay! The upcoming Thanksgiving holiday has definitely strained the schedule. This is similar to #1105 . Excel does not round in some cases. As a simple example, try setting A1=0.1+0.2 and A2=0.3, then format both as Fraction with up to 1 digit. That will render "2/7" in A1 and "1/3" in A2. When looking at the file in XLSX, the value in A1 is the exact IEEE754 value with the ULP (`0.30000000000000004`) while the value in A2 is the "rounded" value (`0.3`) So clearly some part of the formatting process cares about the exact value. Bisecting by editing an XLSX file in Vim, 0.14499999999999950 => 0.14 and 0.14499999999999951 => 0.15 (see attached) Can you confirm you see the same results in Excel (try opening the attachment in Excel. A2 is 0.14 and B2 is 0.15)? Do you see the correct formatted values after the patch?

issue2824.xlsx

8.5 KiB

sheetjs commented

2022-11-21 10:18:36 +00:00

Applying the patch only to xlsx.flow.js:

diff --git a/xlsx.flow.js b/xlsx.flow.js
index e7932863..6e98f5e4 100644
--- a/xlsx.flow.js
+++ b/xlsx.flow.js
@@ -646,13 +646,20 @@ function commaify(s/*:string*/)/*:string*/ {
 var pct1 = /%/g;
 function write_num_pct(type/*:string*/, fmt/*:string*/, val/*:number*/)/*:string*/{
 	var sfmt = fmt.replace(pct1,""), mul = fmt.length - sfmt.length;
-	return write_num(type, sfmt, val * Math.pow(10,2*mul)) + fill("%",mul);
+	return write_num(type, sfmt, times_ten_to_power(val, 2*mul)) + fill("%",mul);
 }
 
 function write_num_cm(type/*:string*/, fmt/*:string*/, val/*:number*/)/*:string*/{
 	var idx = fmt.length - 1;
 	while(fmt.charCodeAt(idx-1) === 44) --idx;
-	return write_num(type, fmt.substr(0,idx), val / Math.pow(10,3*(fmt.length-idx)));
+	var d = 3*((fmt.length-idx)*-1);
+	return write_num(type, fmt.substr(0,idx), times_ten_to_power(val, d));
+}
+function times_ten_to_power(val/*:number*/, power/*:number*/)/*:number*/ {
+	var isNegative = Math.sign(val) === -1;
+	var parts = val.toString().split('e');
+	if (isNegative && !parts[0].startsWith('-')) parts[0] = '-' + parts[0];
+	return +(parts[0] + 'e' + ((parts[1] ? +parts[1] : 0) + power));
 }
 
 function write_num_exp(fmt/*:string*/, val/*:number*/)/*:string*/{
@@ -703,7 +710,10 @@ function hashq(str/*:string*/)/*:string*/ {
 	}
 	return o;
 }
-function rnd(val/*:number*/, d/*:number*/)/*:string*/ { var dd = Math.pow(10,d); return ""+(Math.round(val * dd)/dd); }
+function rnd(val/*:number*/, d/*:number*/)/*:string*/ {
+	var dd = Math.round(times_ten_to_power(val, d));
+	return times_ten_to_power(dd, (d*-1))+"";
+}
 function dec(val/*:number*/, d/*:number*/)/*:number*/ {
 	var _frac = val - Math.floor(val), dd = Math.pow(10,d);
 	if (d < ('' + Math.round(_frac * dd)).length) return 0;

The original sample looks correct in NodeJS:

> require("./xlsx").SSF.format("0.00",0.145)
'0.14'
> require("./xlsx.flow").SSF.format("0.00",0.145)
'0.15'

But it does not agree with the Excel rounding rules:

> require("./xlsx.flow").SSF.format("0.00",0.14499999999999950)
'0.14'
> require("./xlsx.flow").SSF.format("0.00",0.14499999999999951)
'0.14'
^^---- this is where Excel starts showing 0.15
> require("./xlsx.flow").SSF.format("0.00",0.14499999999999996)
'0.14'
> require("./xlsx.flow").SSF.format("0.00",0.14499999999999998)
'0.15'

We'll do some more digging and accept the patch if we can't find a satisfactory solution.

Applying the patch only to `xlsx.flow.js`: ```diff diff --git a/xlsx.flow.js b/xlsx.flow.js index e7932863..6e98f5e4 100644 --- a/xlsx.flow.js +++ b/xlsx.flow.js @@ -646,13 +646,20 @@ function commaify(s/*:string*/)/*:string*/ { var pct1 = /%/g; function write_num_pct(type/*:string*/, fmt/*:string*/, val/*:number*/)/*:string*/{ var sfmt = fmt.replace(pct1,""), mul = fmt.length - sfmt.length; - return write_num(type, sfmt, val * Math.pow(10,2*mul)) + fill("%",mul); + return write_num(type, sfmt, times_ten_to_power(val, 2*mul)) + fill("%",mul); } function write_num_cm(type/*:string*/, fmt/*:string*/, val/*:number*/)/*:string*/{ var idx = fmt.length - 1; while(fmt.charCodeAt(idx-1) === 44) --idx; - return write_num(type, fmt.substr(0,idx), val / Math.pow(10,3*(fmt.length-idx))); + var d = 3*((fmt.length-idx)*-1); + return write_num(type, fmt.substr(0,idx), times_ten_to_power(val, d)); +} +function times_ten_to_power(val/*:number*/, power/*:number*/)/*:number*/ { + var isNegative = Math.sign(val) === -1; + var parts = val.toString().split('e'); + if (isNegative && !parts[0].startsWith('-')) parts[0] = '-' + parts[0]; + return +(parts[0] + 'e' + ((parts[1] ? +parts[1] : 0) + power)); } function write_num_exp(fmt/*:string*/, val/*:number*/)/*:string*/{ @@ -703,7 +710,10 @@ function hashq(str/*:string*/)/*:string*/ { } return o; } -function rnd(val/*:number*/, d/*:number*/)/*:string*/ { var dd = Math.pow(10,d); return ""+(Math.round(val * dd)/dd); } +function rnd(val/*:number*/, d/*:number*/)/*:string*/ { + var dd = Math.round(times_ten_to_power(val, d)); + return times_ten_to_power(dd, (d*-1))+""; +} function dec(val/*:number*/, d/*:number*/)/*:number*/ { var _frac = val - Math.floor(val), dd = Math.pow(10,d); if (d < ('' + Math.round(_frac * dd)).length) return 0; ``` The original sample looks correct in NodeJS: ``` > require("./xlsx").SSF.format("0.00",0.145) '0.14' > require("./xlsx.flow").SSF.format("0.00",0.145) '0.15' ``` But it does not agree with the Excel rounding rules: ``` > require("./xlsx.flow").SSF.format("0.00",0.14499999999999950) '0.14' > require("./xlsx.flow").SSF.format("0.00",0.14499999999999951) '0.14' ^^---- this is where Excel starts showing 0.15 > require("./xlsx.flow").SSF.format("0.00",0.14499999999999996) '0.14' > require("./xlsx.flow").SSF.format("0.00",0.14499999999999998) '0.15' ``` We'll do some more digging and accept the patch if we can't find a satisfactory solution.

andrei-kav commented

2022-11-21 15:59:00 +00:00

"Can you confirm you see the same results in Excel (try opening the attachment in Excel. A2 is 0.14 and B2 is 0.15)? Do you see the correct formatted values after the patch?"

yes, in excel i see the same result (A2 is 0.14 and B2 is 0.15). On linux (libreOffice) the result is A2 is 0.15 and B2 is 0.15.
After the putch both values are 0.14.
Tbh i am already confused about what value should be correct)

"Can you confirm you see the same results in Excel (try opening the attachment in Excel. A2 is 0.14 and B2 is 0.15)? Do you see the correct formatted values after the patch?" ----- yes, in excel i see the same result (A2 is 0.14 and B2 is 0.15). On linux (libreOffice) the result is A2 is 0.15 and B2 is 0.15. After the putch both values are 0.14. Tbh i am already confused about what value should be correct)

7.png

11 KiB

sheetjs commented

2022-11-22 01:13:47 +00:00

One of the major challenges is figuring out what is correct, especially since Excel itself has changed behaviors across versions. More than 40 years of history to deal with! (MultiPlan, the predecessor of Excel, was released in summer 1982)

LibreOffice is definitely not correct. We reported a related bug to LO back in 2014 and a developer responded:

We ignore the last two bits for many stuff to improve the user experience.

https://oss.sheetjs.com/notes/lobugs/#rounding-and-ulp-errors for more info.

Numbers 12.2 and Excel 365 (both mac and windows) agree on the interpretation. It might be possible to do some string manipulation:

(0.14499999999999950).toPrecision(17).slice(0,-1); // 0.1449999999999994
(0.14499999999999951).toPrecision(17).slice(0,-1); // 0.1449999999999995

One of the major challenges is figuring out what is correct, especially since Excel itself has changed behaviors across versions. More than 40 years of history to deal with! (MultiPlan, the predecessor of Excel, was released in summer 1982) LibreOffice is definitely not correct. We reported a related bug to LO back in 2014 and a developer responded: > We ignore the last two bits for many stuff to improve the user experience. https://oss.sheetjs.com/notes/lobugs/#rounding-and-ulp-errors for more info. Numbers 12.2 and Excel 365 (both mac and windows) agree on the interpretation. It might be possible to do some string manipulation: ```js (0.14499999999999950).toPrecision(17).slice(0,-1); // 0.1449999999999994 (0.14499999999999951).toPrecision(17).slice(0,-1); // 0.1449999999999995

andrei-kav commented

2022-12-15 10:22:58 +00:00

@sheetjs good day! Could you please clarify if there are any updates on these points?
Thank you in advance!

@sheetjs good day! Could you please clarify if there are any updates on these points? Thank you in advance!

petercoppensdatylon commented

2023-02-10 10:30:41 +00:00

Hello,

As requested through email, details on the issue mentioned in the email.

I work with andrei-kav and so the specific case which triggered the email and, earlier, this thread is listed higher and available in https://git.sheetjs.com/attachments/3cb8dc4a-3f09-483e-9774-65d61e293b23

To summarize. The sheet has '14.5%' but formatted with 1 decimal. It comes back as 14 from sheetjs where we would expect 15 (which is what Excel displays)

Tx!

Peter

Hello, As requested through email, details on the issue mentioned in the email. I work with andrei-kav and so the specific case which triggered the email and, earlier, this thread is listed higher and available in https://git.sheetjs.com/attachments/3cb8dc4a-3f09-483e-9774-65d61e293b23 To summarize. The sheet has '14.5%' but formatted with 1 decimal. It comes back as 14 from sheetjs where we would expect 15 (which is what Excel displays) Tx! Peter

Sign in to join this conversation.