What function can I use to remove excessive trailing zeroes after the decimal point?
I have values like 123.00000 123.40000 123.45000 123.45600 123.45670 123.45678
I do not want any unnecessary trailing zeroes; just one if it is x.0
Those "ridiculous little errors" are artifacts of the translation of base 10 (decimal) numbers that include a fractional part being translated to floating point base 2 (binary) numbers of fixed precision for calculation by the computer, then being translated back to base 10 for display.
The problem is a simple one: there are decimal fractions that can be represented exactly in base 10 that simply cannot be represented exactly in fixed precision floating point binary numbers.
Binary floating point calculations follow The IEEE Standard for Floating-Point Arithmetic (IEEE 754), originally written in 1985, the current version (according to the Wikipedia article on the topic) is the 2008 revision.
For the precision expected in 'ordinary' arithmetic, rounding your final result to a set number of places after the decimal will usually suffice. A general rule of thumb is to round final results to a precision no greater than the lowest precision of any input value obtained by measuring.
For either of the 'ridiculous small error' examples supplied above, rounding to any where from 4 to 'one fewer than displayed in the example' places after the decimal should produce the precise result you expect. Rounding to four places will produce that result with no trailing zeros.
ROUND(formula,4)
Regards,
Barry
select the cells in question, then use the cell formatter to adjust the number of decimal places :
Before formatting:
After formatting:
Hi Apophis,
Try this:
Format Panel > Cell > Data Format > Number > Decimals > Auto
Regards,
Ian.
"Yes, that works to some degree, but I've also found it introduces ridiculous little errors in consecutive numbers, like 123.45678 suddenly becoming 123.45678000001 !!!"
How are these numbers produced? By formula? Entered directly?
Regards,
Barry
Thanks. My point in describing this problem as "ridiculous little errors" is whilst that there may well be a technical detailed explanation of why it happens, the average user should not have to read up on IEEE 754 to understand out such technical problems. They should be dealt with automatically by the software.
I have ended up appending another column for the calculated value (including the numerous decimal places), with the original column now showing the "ROUND" value.
"Deal(ing) with them automatically" requires knowing the purpose of individual uses of the calculations displaying FP Binary errors. Some spreadsheet developers have chosen to guess that, and have included automatic 'corrections' to fit that guess. Others have chosen to implement IEEE 754 in a manner that permits the individual user to apply no corrections or to choose corrections that fit what they are actually doing, rather than rely on a developer correctly guessing.
If you think Apple has made the wrong choice, and would like to see that corrected, then you should be taking to Apple. Here, your audience is users of the software, not the development team.
Numbers menu,
Provide Numbers Feedback.
Make your request for a feature enhancement to fit your needs.
Regards,
Barry
Except I don't want ONE decimal place, I want however many decimal places is appropriate. In my case this can be anything from 1 to 5.
Yes, that works to some degree, but I've also found it introduces ridiculous little errors in consecutive numbers, like 123.45678 suddenly becoming 123.45678000001 !!!
But then the trailing zeroes re-appear. It's like Catch 22.
By formula, such as by adding 0.01234 to the previous value.
All works fine at first, but further down the sheet these ridiculous little errors like 0.002499999999 start appearing instead of 0.0025!
Use the cell formatter to set the cell formatting selection to "Number" and number of places "Automatic"
How do I remove trailing zeroes?
