Numbers Rounding #2824

Open
opened 4 weeks ago by andrei-kav · 6 comments

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!
Owner

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`.
Poster

You are welcome! Are you going to fix it?

You are welcome! Are you going to fix it?
Owner

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?
Owner

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.
Poster

"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)
11 KiB
Owner

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
Sign in to join this conversation.
No Milestone
No project
No Assignees
2 Participants
Notifications
Due Date

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2824
Loading…
There is no content yet.