Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Looks like no one’s replied in a while. To start the conversation again, simply
ask a new question.
I've been using a checksum cell to ensure I've entered data correctly and have noticed that Numbers is inaccurate regularly, where it adds decimal places where it shouldn't. Here is a repeatable example. Notice that there are no extra decimal places, so the error is almost certainly Numbers'.
If you add 6206.69
657.271402.71 the total is 8266.67.
If you then subtract 8266.67 from the sum, you don't get a nice clean 0, you get -1.81899E-12.
You don't get the same error in Excel, calculator, etc.
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)
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)
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)
I'm well aware of the issue of floating point calculations, but to have such an error when you only have 2 decimal places of accuracy anywhere in the spreadsheet is a bug, not a feature of floating point calculations. Apple's own calculator manages the above calculation fine.
I only discovered this as I was using an IF formula to display "Error!" if the checksum wasn't zero to make sure data had been entered correctly. It took me a while to work out why I had an "Error" when I could find no trace... It wasn't MY error, it was Numbers'.
Obviously a ROUND formula fixes the problem, but it really shouldn't be there. We're not talking about complicated sums, look at the calculation above! Adding a few numbers as currency, with never more than 2 decimal places required should not produce the error it does.
It's a bug. Pure and simple.
(6206.69
657.271402.71)-8266.67=0 however you calculate it. Numbers thinks it's -1.81899E-12.
I'm well aware that floating point arithmetic cannot completely match true arithmetic, but in this case we're not talking about small errors becoming significant due to cumulative calculations. We're talking about a simple 2 decimal place sum. This sort of calculation really should be caught by the software (Numbers) as it obviously is by Apple's own calculator and Excel.
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.
No, I am not wrong. If you take the time to re-read what I posted you will see I'm not talking about numbers formatted as currency, but numbers that have ALREADY been rounded (or have only 2 decimal places already).
Try this (repeated from my post above):
=(6206.69
657.271402.71)-8266.67
There you go. There are only 2 decimal places in all of those number. The result should be zero.
What do you get?
If it's not ZERO it is wrong. There's no other way of describing this level of calculation error. There are no accumulated errors from VAT calculations, no hidden decimal places.
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)