No Branch/Tag Specified

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.87

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.11

v0.7.10

v0.7.1

v0.5.9

v0.5.8

v0.5.7

v0.5.10

v0.5.0

v0.4.3

v0.18.6

v0.18.5

v0.18.4+deno

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0+deno

v0.17.5

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.1

v0.12.0

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

**Milestone**

Set milestone

Clear milestone

No items

No Milestone

**Projects**

Set Project

Clear projects

No project

**Assignees**

Assign users

Clear assignees

No Assignees

**3 Participants**

**Notifications**

**Due Date**

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

**Dependencies**

No dependencies set.

Reference: sheetjs/sheetjs#2824

Reference in New Issue

There is no content yet.

Delete Branch '%!s(<nil>)'

Deleting a branch is permanent. It **CANNOT** be undone. Continue?

No

Yes

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.xlsx1.png2.pngThanks for reporting! This appears to be an error in the rounding logic, since the file has the correct value

`0.145`

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

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.xlsxApplying the patch only to

`xlsx.flow.js`

:The original sample looks correct in NodeJS:

But it does not agree with the Excel rounding rules:

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

"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.pngOne 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:

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:

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

Thank you in advance!

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