Formating

[Guest]

I have a cell formated with 'Accounting' and if I enter a zero a line
appears. I copied that format to other cells but a few of the cells show
0.00 instead. I check the formating and it looks the same. Where do I
correct this. I want them all to show as a line.

Thank You,
Julie

 

[Guest]

okay, when i extend the decimal point out to about 27, it shows there are
some numbers. i am only adding and subtracting in all of the formulas on this
spreadsheet. I check all of the cells and none of them have more than 2
decimals. So I am still confused.

 

[Guest]

It's a precision thing. Use a formula such as =ROUND(.....,5) around your
current values. Excel doesn't see them as 0, but as teeny numbers, as you
discovered.

 

[Guest]

Thank you but i don't know where the odd numbers are comming from.

 

[David Biddulph]

Rounding in fixed point.
If you've got a number like 0.23, try to work out what binary number gives
that number exactly. Same for 0.77. Add the two together and subtract one,
and you hope you'll get zero, but as the original binary representations
weren't exact, the answer may not be exactly zero.
You could try =ROUND(your_formula,2)

 

[Guest]

The only 2-decimal place numbers that can be exactly represented in binary
are .00, .25, .50, and .75. The rest are non-terminating binary fractions
that can only be approximated in binary (just as 1/3 is a non-terminating
decimal faction that can only be approximated in decimal). When you do math
with approximate inputs, it should be no surprise that your results will only
approximate what you intended.

For example, the decimal representations of the binary approximations to
0.3, 0.2, and 0.1 are
0.299999999999999988897769753748434595763683319091796875
0.200000000000000011102230246251565404236316680908203125
0.1000000000000000055511151231257827021181583404541015625
so that =(0.3-0.2-0.1) correctly returns
-2.77555756156289135105907917022705078125E-17
instead of zero. You can't directly see the approximations to 0.3, 0.2, and
0.1 because Excel (as documented in Help) will display no more than 15
digits, but you can indirectly see them in the result of this calculation.

With your calculations, you know that you are only adding and subtracting
numbers that nominally have nothing beyond the 2nd decimal place, therefore
rounding to 2 decimal places does no violence to your intended calculations,
but does reduce the impact of the binary approximations.

Jerry