Bug 64620

Summary:	Apache POI API evaluation value - Excel value - not matching
Product:	POI	Reporter:	Avinash M <avinash>
Component:	XSSF	Assignee:	POI Developers List <dev>
Status:	RESOLVED INFORMATIONPROVIDED
Severity:	normal
Priority:	P2
Version:	4.1.2-FINAL
Target Milestone:	---
Hardware:	PC
OS:	All

Description Avinash M 2020-07-22 13:04:43 UTC

We have entered following formula in the excel
   
   =3000/12*(1-0.8)

The above formula will result to 50.


Evaluating this formula through Apache poi Java library resulting to 49.99999.

As our application runs on numbers, the out put results doesn't match to the expected results.

Please comment.

Thanks!!

Comment 1 west wong 2020-07-27 03:18:51 UTC


new BigDecimal(3000/12*(1-0.8)).setScale(0,RoundingMode.HALF_UP);

The value of newScale denpends on what you need !

I usually use setScale(2,RoundingMode.HALF_UP);

Comment 2 PJ Fanning 2021-10-08 20:37:09 UTC

Excel files store numbers as doubles and doubles are just approximations. If you use the POI DataFormatter class, it will apply a format based on the cell style and this can include rounding the number to the form displayed by Excel.