Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error with decimals
Has anyone notice a problem with up decimal, somehow excel adds up more
decimals. eg. cell (A1) 5045.3 cell (A2) 5045.03 cell (A3) =A1-A2 Note that the diference will be .27 But excel brings 0.270000000000437 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error with decimals
Welcome to the world of computer math!
Computers (no just Excel) generally store numbers in binary format (base 2) rather than decimal (base 10) - we have 10 fingers while a computer knows only ON and OFF. The conversion is done following the IEEE protocol which specifies 15 digits (this is a bit of a simplification). Some decimal numbers (real or floating point numbers with fractional parts but not integers) cannot be exactly represented in this binary form. So we get "round-off" errors due to the conversion to and from binary/decimal Read more at http://support.microsoft.com/kb/78113/en-us One way to solve this is to use =ROUND(formula, some number less than 15) as in =ROUND(A1-A2,10) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Leamsi" wrote in message ... Has anyone notice a problem with up decimal, somehow excel adds up more decimals. eg. cell (A1) 5045.3 cell (A2) 5045.03 cell (A3) =A1-A2 Note that the diference will be .27 But excel brings 0.270000000000437 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error with decimals
If you want to expand on Bernard's answer, you might find the functions at
http://groups.google.com/group/micro...06871cf92f8465 to be useful. Using them, you will see that the IEEE standard approximations to 5045.3 and 5045.03 are 5045.3000000000001818989403545856475830078125 5045.0299999999997453414835035800933837890625 whose difference is 0.27000000000043655745685100555419921875 which Excel correctly reports to its documented limit of 15 digits. Bottom line: the math is right, but most decimal fractions must be approximated, leading to approximate results. Jerry "Leamsi" wrote: Has anyone notice a problem with up decimal, somehow excel adds up more decimals. eg. cell (A1) 5045.3 cell (A2) 5045.03 cell (A3) =A1-A2 Note that the diference will be .27 But excel brings 0.270000000000437 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Excel data to word, keeps including all decimals on number | Excel Discussion (Misc queries) | |||
Convert $ amount from excel to access? No decimals but 00 at end | Excel Worksheet Functions | |||
Conditional formatting : amount of decimals | Excel Discussion (Misc queries) | |||
Comma format button decimals | Excel Discussion (Misc queries) | |||
How do I calculate without decimals in excel? | Excel Worksheet Functions |