Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
wrongly added or subtracted decimals
Suddenly I have started problem with simple calculations (addition and
subtraction). I have starting values in the first column wtih maximum two significant figures. I then perform a few operations (addition and subtraction) using numbers from a previous operation(s) for a following operation. For instance, 1) the original value, 2.875, 2) 100-2.875, then 3) the answer from 2) - 95.15. The final answers should be exactly 1.075, but when I extend a number of decimal places, the value has a small value either added or subracted from the supposed to be answers. For example, instead of 1.075, it shows 1.0750000000001200. I have tried to correct this error without any success. Does anyone have any idea how to correct this problem? none |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
wrongly added or subtracted decimals
This is not YOUR problem; it results from the way computer convert decimal
numbers to binary. See Excel IEEE error http://support.microsoft.com/kb/78113 Your solution is =ROUND(A1-B1, 10 ) to get 1.0750000000 by the way: what do you mean by "I have starting values in the first column with maximum two significant figures" The value 2.875 has 4 sig fid (or 3 decimal places) while 95.15 has 4 sig figs (but 2 decimal places) "Sig figs" is NOT a synonym for "number of decimal places" Forgive an old professor! -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ornithology 1" wrote in message ... Suddenly I have started problem with simple calculations (addition and subtraction). I have starting values in the first column wtih maximum two significant figures. I then perform a few operations (addition and subtraction) using numbers from a previous operation(s) for a following operation. For instance, 1) the original value, 2.875, 2) 100-2.875, then 3) the answer from 2) - 95.15. The final answers should be exactly 1.075, but when I extend a number of decimal places, the value has a small value either added or subracted from the supposed to be answers. For example, instead of 1.075, it shows 1.0750000000001200. I have tried to correct this error without any success. Does anyone have any idea how to correct this problem? none |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
wrongly added or subtracted decimals
Excel's math is correct, but most terminating decimal fractions (including
95.15 and 1.075) are non-terminating binary fractions that must be approximated. When you do math with approximate inputs, it should be no surprise when the output is also only approximate. As Bernard has noted, the level of approximation is defined by IEEE 754 standard for double precision calculations (followed by almost all general purpose software). Using the VBA functions from http://groups.google.com/group/micro...06871cf92f8465 =D2D(95.15) gives 95.150000000000005684341886080801486968994140625 as the exact value of the approximation to 95.15. Do the math; 97.125 minus this value is 1.974999999999994315658113919198513031005859375 which Excel correctly reports to its documented limit of 15 figures as 1.97499999999999. Jerry "ornithology 1" wrote: Suddenly I have started problem with simple calculations (addition and subtraction). I have starting values in the first column wtih maximum two significant figures. I then perform a few operations (addition and subtraction) using numbers from a previous operation(s) for a following operation. For instance, 1) the original value, 2.875, 2) 100-2.875, then 3) the answer from 2) - 95.15. The final answers should be exactly 1.075, but when I extend a number of decimal places, the value has a small value either added or subracted from the supposed to be answers. For example, instead of 1.075, it shows 1.0750000000001200. I have tried to correct this error without any success. Does anyone have any idea how to correct this problem? none |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the number format for decimals from , to .? | Setting up and Configuration of Excel | |||
How do I calculate without decimals in excel? | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Links pull wrong line when rows are added | Excel Discussion (Misc queries) | |||
Decimals separation by . or , | Excel Discussion (Misc queries) |