search

dgorissen / pycel

A library for compiling excel spreadsheets to python code & visualizing them as a graph
GNU General Public License v3.0
565 stars 147 forks source link

Incorrect formual evaluation #149

Closed maneeshkulkarni78 closed 2 years ago

maneeshkulkarni78 commented 2 years ago

What actually happened

I have an excel formula as "=B5-C5-D5-E5". This should evaluate to 0.0 whereas it get evaluated to 1.3969838619232178e-09. I observed if I remove the 4th Cell reference (E5) from formula then I get correct evaluation. So I replaced E5 with 0 and still got 1.3969838619232178e-09.

What was expected to happen

The formula evaluation should return 0.

Pycel version = 1.0b22

dgorissen commented 2 years ago

Thats a numerical issue (https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems) and e-9 is for all intents and purposes 0 unless you are dealing with a very specific application where that level of accuracy matters. Though in that case Id question the use of Excel tbh.

bauerjon commented 1 year ago

@maneeshkulkarni78 @dgorissen in case anyone else needs it, here is a patch I used on our fork of pycel for floating point math. It issues decimal math to ensure it more properly aligns with excels output

https://github.com/ObieCRE/pycel/compare/241cabe0610425af2d41b3e997e4cc1e628cb87e...5f1a764300dbe5de96fe53a0f0ced7a6f90fa1bf