Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Forum for the beta test of PlanMaker 2021 for Windows
Post Reply
jens_ulrik
Posts: 9
Joined: Sun Jun 01, 2014 8:29 pm

Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by jens_ulrik » Fri May 15, 2020 2:29 am

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 (64-bit):

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 11 times

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 587
Joined: Wed Mar 11, 2020 5:31 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by SuperTech » Fri May 15, 2020 8:53 am

Thank you for reporting this issue which I have forwarded to the product development team.

bennachie
Posts: 66
Joined: Mon Sep 20, 2010 3:49 am

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by bennachie » Fri May 15, 2020 9:24 am

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.0-alpha, 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, long-standing issues in Planmaker that don't exist in LibreOffice.

jens_ulrik
Posts: 9
Joined: Sun Jun 01, 2014 8:29 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by jens_ulrik » Fri May 15, 2020 3:01 pm

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

martin-k
SoftMaker Team
SoftMaker Team
Posts: 2767
Joined: Fri Nov 21, 2003 4:57 pm
Location: Nürnberg, Germany
Contact:

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by martin-k » Fri May 15, 2020 3:06 pm

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 infinitive-precision math library which will be slow because it has to be done in software instead of hardware.
Martin Kotulla
SoftMaker Software GmbH

jens_ulrik
Posts: 9
Joined: Sun Jun 01, 2014 8:29 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by jens_ulrik » Fri May 15, 2020 4:43 pm

@martin-k: 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.

User avatar
mgrinsven
Posts: 67
Joined: Mon Feb 10, 2020 9:40 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by mgrinsven » Fri May 15, 2020 7:38 pm

Just to make this problem more interesting:
Ubuntu 20.04 - Default Calculator:
calc.png

PeterMc
Posts: 17
Joined: Sun Apr 16, 2006 10:05 am

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by PeterMc » Fri May 15, 2020 11:14 pm

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

jens_ulrik
Posts: 9
Joined: Sun Jun 01, 2014 8:29 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by jens_ulrik » Sat May 16, 2020 8:01 pm

No, not really. Most calculators/spreadsheets return 1,049213715824304 when multiplying with 0,000000000000002. But I'm multiplying with 1,776356839400251E-15 (or at least I think I am...)

/JUJ
mgrinsven wrote:
Fri May 15, 2020 7:38 pm
Just to make this problem more interesting:
Ubuntu 20.04 - Default Calculator:

calc.png

jens_ulrik
Posts: 9
Joined: Sun Jun 01, 2014 8:29 pm

Re: Calculation-error (PM2021 multiplies with FORMATTED value and not with numeric value)?

Post by jens_ulrik » Sat May 16, 2020 8:16 pm

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
PeterMc wrote:
Fri May 15, 2020 11:14 pm
This may (or may not) be relevant and interesting. Note that 'precision as displayed' is considered to be a legitimate tactic in some cases.

Post Reply

Return to “BETA PlanMaker 2021 for Windows”