Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?

 Posts: 9
 Joined: Sun Jun 01, 2014 8:29 pm
Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
See attached spreadsheet that I made with PlanMaker 2016.
I'm dividing 524.606.857.912.152 with 562.949.953.421.312 in four different ways in PlanMaker 2021 Beta rev S1009.0505 (64bit):
1. Actual division. Result (0,931888980048527) looks alright.
2. Multiplying with inverse of 562.949.953.421.312 (formatted as Number with 15 decimals). Result (1,049213715824304) is disturbingly not alright.
3. Multiplying with inverse of 562.949.953.421.312 (formatted as Scientific with 15 decimals). Result (0,931888980048527) may be alright, but I'm not sure.
4. Multiplying with inverse of 562.949.953.421.312 (unformatted). Result (0,931888980048526) differs in the 15th decimal.
In PlanMaker 2016 I get 4 identical results.
What is happening here?
I'm dividing 524.606.857.912.152 with 562.949.953.421.312 in four different ways in PlanMaker 2021 Beta rev S1009.0505 (64bit):
1. Actual division. Result (0,931888980048527) looks alright.
2. Multiplying with inverse of 562.949.953.421.312 (formatted as Number with 15 decimals). Result (1,049213715824304) is disturbingly not alright.
3. Multiplying with inverse of 562.949.953.421.312 (formatted as Scientific with 15 decimals). Result (0,931888980048527) may be alright, but I'm not sure.
4. Multiplying with inverse of 562.949.953.421.312 (unformatted). Result (0,931888980048526) differs in the 15th decimal.
In PlanMaker 2016 I get 4 identical results.
What is happening here?
 Attachments

 multiply_with_formatted_value2.xlsx
 (6.23 KiB) Downloaded 12 times
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
Thank you for reporting this issue which I have forwarded to the product development team.
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
That's very interesting, especially since Excel gives the same results. It's good to see that it has been promptly reported to "product management", but I wouldn't hold your breath. Perhaps there has been a conscious decision by the developers to replicate Excel errors rather than to return the correct answers? Only joking  it seems much more likely that the error is just an example of convergent evolution.
You might like to note that LibreOffice 7.0.0alpha, which I'm also testing, does give the correct answers. I haven't checked any earlier versions, but I suspect they would do likewise. Perhaps the Planmaker developers should spend some time reviewing the LibreOffice source code, which is readily available online. They do seem to be having some difficulty dealing with several other serious, longstanding issues in Planmaker that don't exist in LibreOffice.
You might like to note that LibreOffice 7.0.0alpha, which I'm also testing, does give the correct answers. I haven't checked any earlier versions, but I suspect they would do likewise. Perhaps the Planmaker developers should spend some time reviewing the LibreOffice source code, which is readily available online. They do seem to be having some difficulty dealing with several other serious, longstanding issues in Planmaker that don't exist in LibreOffice.

 Posts: 9
 Joined: Sun Jun 01, 2014 8:29 pm
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
@bennachi: Thanks for testing that. I also tried in Online Excel (office.live.com), Google Sheets and SoftMaker Office Mobile for Android. It seems like Excel has a big problem with precision.

 SoftMaker Team
 Posts: 2768
 Joined: Fri Nov 21, 2003 4:57 pm
 Location: Nürnberg, Germany
 Contact:
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
Are we talking about imprecisions in the fifteenth place after the comma? All calculations are handled by the FPU of your computer, and around the 15th place is the point where precision simply ends. It might be that LibreOffice masks the imprecisions better, but they will still be there unless they implement their own infinitiveprecision math library which will be slow because it has to be done in software instead of hardware.
Martin Kotulla
SoftMaker Software GmbH
SoftMaker Software GmbH

 Posts: 9
 Joined: Sun Jun 01, 2014 8:29 pm
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
@martink: No, it's not about differences on the 15th decimal. It's about expecting 0,931888980048527, but getting 1,049213715824304. I'm dividing a smaller number with a bigger number, and I'm getting a result larger than 1.
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
Just to make this problem more interesting:
Ubuntu 20.04  Default Calculator:
Ubuntu 20.04  Default Calculator:
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
This may (or may not) be relevant and interesting. Note that 'precision as displayed' is considered to be a legitimate tactic in some cases.
Peter McLennan
Dell Axim X51v, Windows Mobile 5, Dell (Think Outside) keyboard, 4GB SD card
Dell Axim X51v, Windows Mobile 5, Dell (Think Outside) keyboard, 4GB SD card

 Posts: 9
 Joined: Sun Jun 01, 2014 8:29 pm
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
No, not really. Most calculators/spreadsheets return 1,049213715824304 when multiplying with 0,000000000000002. But I'm multiplying with 1,776356839400251E15 (or at least I think I am...)
/JUJ
/JUJ

 Posts: 9
 Joined: Sun Jun 01, 2014 8:29 pm
Re: Calculationerror (PM2021 multiplies with FORMATTED value and not with numeric value)?
Thanks for the link. It appears that both Excel and PlanMaker 2021 use "Precision as displayed".
I can't find any option that turns this off in PlanMaker 2021.
Also I don't think "Precision as displayed" should be turned on by default.
/JUJ
I can't find any option that turns this off in PlanMaker 2021.
Also I don't think "Precision as displayed" should be turned on by default.
/JUJ