Field calculation bug

The problem

When entering calculation formulas into numerical fields, results are incorrect in some cases. In the following example, inventory item purchase and sales prices are used for illustration. But the bug seems to exist anywhere calculations are permitted.

For the illustration, calculations of [100 * X] were entered.

  • When X was 1.085 or below, the result was correct.
  • When X was 1.0851–1.152, results were off in the 14th decimal place, sometimes higher and sometimes lower than the exact result should have been. This suggests the problem is related to floating point arithmetic.
  • When X was greater than 1.152, results were again correct.

The inputs

Specific values of X used in the illustration were as shown in the table below, arranged to match the layout of the output screen shot below:

Column 1 Column 2
1.04 1.085
1.0851 1.1
1.15 1.151
1.152 1.1521
1.5 1.8

The output

Manager’s calculated values of [100 * X] are shown below:

59%20AM

While differences at the 14th decimal place might seem insignificant, and do not matter much on a tab listing, this problem also shows up when calculations are used on transaction forms. For example, a calculated unit price of [217.12 * 1.1] yields:

33%20AM

The exact unit price is 238.832. (Of course, in this example, an error in total amount would not show up unless the quantity was very large.)

Note:
Testing did not reveal another range where calculation results are erroneous. But that does not mean other ranges do not exist, only that they were not found.

1 Like

Hi Tut
This bug as you called it is a programming error inside Manager that need to be fixed.
Here is a screenshot of the calculations I entered in to a spreadsheet and then I created an inventory item in Manager and when I entered the calculations inside Manager then I got another answer that is incorrect.

So this what you suggest here is not going to work, you will not get the right answer even if you try. You can enter the calculations directly into the sales price field. See https://www.manager.io/guides/18222 . Note that there is a new bug report that has a tiny impact on this. (This is not a tiny impact but a very very big one.)

The formula that I use in the spreadsheet and the one in Manager is the same (100/(100-30))*3.3

Blessings

No, they are not the same. Your D2 cell in the spread sheet is not 3.3, as you entered in Manager. It is 3.35. Therefore, your screen shots do not illustrate any problem at all. They illustrate the result of two completely different calculations.

Well I fixed my mistake and still the answer is incorrect. I highlight it. I use 20 decimal after the comma and mine stops at 90000 where Manager stops with a 86

@BillionDollars, you are just illustrating the limits of floating point arithmetic.

The solution to calculation accuracy limitations is use of a round function, set to above the underlying calculation accuracy. Or am I missing something here?

@Patch, Manager’s in-field calculation feature permits only arithmetic expressions and does not accept alphabetic input. See Perform calculations in number fields | Manager. Even if it did, the expression [100 * 1.1] produces an exact integer, 110. No rounding should be necessary. In fact, all the examples in the illustration should produce numbers that are exact at no more than two decimal places.

I would be surprised if manager ever did exact arithmetic.
It is much more likely decimal numbers are converted to a binary approximations. Then microprocessor approximation are used for addition, subtraction, multiplication and division. Multiplication results in twice as many digits, those beyond the number format are discarded. Division often result in an infinite number sequence, those beyond the number format are discarded. Subtraction can result in numbers much closer to zero with a very low relative precision. The binary answer is then converted back to a decimal approximation.

Lubos has been hiding this detail mostly for currencies by rounding the result to the nearest cent (the exception include GST on items not being the same as GST on the total, and currency conversions). Spreadsheets also hide the detail by rounding to fit the display cell size and allowing user specified display precision.

With user calculations in manager, the units are not known to Lubos so he can’t round to hide the calculation/ number format conversion approximations.

The solution is to support user specified rounding precision. So this should be an enhancement request/ idea not a bug report.

Computers can do “exact” arithmetic, however the answer display would not be suitable for the typical Manager user and require a major complexity programming enhancement for Lubos.

PS
Double precision floating point numbers have a precision of about 14 decimal digits. As this is the most common floating point format, it is probably what is used by Manger.

Lubos may be able to display number rounded to say 10 significant decimal digits and hide most floating point calculation limitations.

Should be fixed in the latest version (19.2.94)