Calculation errors in Numbers

Hello

Always the same question asked ever and ever.

Internal calculations are made on floating numbers.

The transfer between what you enter in a formula and what is really used by the application to make calculations introduces some ∂ at the very end of numbers.
We may get rid of that with a formula of this kind:

=ROUND((6206.69 657.271402.71)-8266.67,11)

You may *go to "Provide Numbers Feedback" in the "Numbers" menu*, ask why they failed to do the needed rounding before displaying the results.
Then, cross your fingers, and wait at least for iWork'09 😉

AppleWorks works the same since it's first delivery (as ClarisWorks) in 1991.

Yvan KOENIG (from FRANCE lundi 28 janvier 2008 16:17:16)

Hi,

Automatic Cell Formatting reveals the warts that Yvan explains. Pre-formatting the cell to Number, Decimals 2 will solve your problem.

Jerry

Jerrold Green1 wrote:
Hi,

Automatic Cell Formatting reveals the warts that Yvan explains. Pre-formatting the cell to Number, Decimals 2 will solve your problem.

Jerry

Not really

the number will appear corrct but its true value will be the one with the extraneous ∂.

For guys using a spreasheet to build bills it may be annoying because in this case a (sum of values) may be displaid different that the (value of the sum).

This is why during 20 years (with appleWorks) I used
ROUND(prixHT*1.196,2)
and not
prixHT*1.196

Yvan KOENIG (from FRANCE lundi 28 janvier 2008 23:22:36)

I guess it would especially be a problem if you needed to test for zero.

Jerry

Hello

zero had nothing to do with the problem.

I had ONE list of public prices (VAT integrated)

When I had to edit a bill for a professional, I was forced (it's law here) to edit a bill with unit prices without the VAT.

So, at the very beginning, for each object the cell containing an unit price was
nakedPrice =VATprice/1.196
the next column contained the quantity
the next one, partialTotal =was nakedPrice * quantity
these on several rows and, at the bottom
=SUM(partialTotal)

Alas, when someone was entering in a calculator the sum displaid in every partialTotal, the result was not the value displaid at the very bottom.

When the "at the_bottomtotal" is smaller than the cumul, the customer didn't complain but in the alternate situation my phone was quickly ringing.

This is why I had to replace:
nakedPrice =VATprice/1.196
by
nakedPrice =ROUND(VATprice/1.196,2)

After that, the problem was gone.

Yvan KOENIG (from FRANCE mardi 29 janvier 2008 9:42:28)

Hello
From my point of view, you are wrong.

The currency format has nothing to do with the treated constants.

When I calculate the VAT corresponding to a neat price 123.45
the value of the product IS 24.1962.

The currency format will display it as 24.20 but the value remains 24.1962

Do that in ten rows
and enter a formula calculating the sum of the ten 24.1962 values

In automatic format, the result will be 241.962 which is perfectly correct.

Apply the currency format which I repeat, is a format, not a convert value tool.
The ten initial values will appear as 24.20 which is normal

but the total will appear as 241.96 which is normal as it is the correct representation of 241.962 in currency format but is perfectly odd for the customer which reads that 10 times 24.20 is 241.96 when he perfectly knows that it is 242.00

It's definitely not a bug in the app but it's because the way it was used in the sample is wrong.

If one wants that results displaid with the currency format match the calculations done of the currency values he MUST take care to work with values matching the currency format which means that he must use the ROUND(value,2) formula.

I assumes that my wording is far from perfect and so, I hope that some one more fluent with english may have time enough to rewrite my explanations.

Yvan KOENIG (from FRANCE mercredi 30 janvier 2008 14:09:40)

Yvan emailed a request to edit his post. Although I think what he's said is clear, here's the requested edit.
Barry

From my point of view, you are wrong.

The currency format affects only the display, and does not change the underlying values.

When I calculate the 19.6% VAT on an item with a net price 123.45
the true value of the VAT is 24.1962.

The currency format, set to a precision of 2 (places after the decimal) will display that as 24.20 but the actual value remains 24.1962

Repeat that calculation in ten rows and enter a formula calculating the sum of the ten VAT values.
In automatic format, the result will be 241.962 which is the correct sum of the 10 actual 24.1962 values.

Apply the currency format which I repeat, changes the display, but does not convert the value.
The ten initial values will appear as 24.20 which is normal, but the total will appear as 241.96 which is normal as it is the correct representation of 241.962 to the two decimal place default of the currency display format, but may not match the customer's expectation given the 24.20 value displayed in the 10 cells being summed. That customer will expect a sum of 242.00.

It's definitely not a bug in the app but a problem of expectations not matching results.

If one wants that result, displayed with the currency format, to match the results of calculations done using the displayed currency values he MUST take care that the values used in the calculations match the values displayed. The only way to do that is to round the actual values, rather than use Number Format > Currency to display rounded versions of the actual values. To calculate using the rounded values, he must use the the ROUND(value,2) formula.

Post by Yvan,
Revised wording by Barry

Hello

How may I write that.

Every decimal number entered in an Apple spreadsheet is treated internally as a _floating number_ which means that it is seen as a serial development.

There is no intermediate fixed format.
This point was widely debated when Apple introduced its set of numbers crunching (SANE) tool.
Of course some ones where claiming that an intermediate fixed format was needed. But read Apple Human Interface Guidelines. The decisions are not trying to match the needs of 100% of the users but to match 80%.
You are just trying to re-open a more than 30 years old debate (if my memory is right, the mac was introduced in 1988 and SANE already existed).

The floating number representation is ALWAYS an approximation.
It's true for 123.45 as well as for PI.
The extraneous value is in the e power -12 range

It is exactly the same in AppleWorks.

If you want that your numbers are treated internally as we humans are seeing them, you must work with integers. This is why some professionals of accounts which I know are not working with euros but with _cents of euros_ which allow them to be free of this epsilon garbage.

If I uses this formula
=620669 65727140271-826667 it returns zero
because it works with integers
If I edit it like that,
=(((6206.69 100)(657.27*100)(1402.71*100))-(8266.67100)) it returns zero
because every added/substracted item is seen as an integer too
If I complete it by a division by 100
=(((6206.69 100)(657.27*100)(1402.71*100))-(8266.67100))/100
the result is zero because _zero divided by any non null value_ is _internally treated as zero_.

My own choice is not this one.
I uses the ROUND() function.

Now, if you are interested to rant about this feature, it's your problem.
I just wish to write that I would be surprised if Apple was modifying what was defined as its standards in the numbers crunching area.
For me, which have no time to loose with ranting, the question is closed, PERIOD!

Yvan KOENIG (from FRANCE vendredi 8 février 2008 14:01:14)