Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding error is conditional on brackets
If I put these values and formulae, I get a zero total, which is what I expect
a1: 123.456 a2: 234.567 a3: 358.023 =a3-a2-a1 (gives zero exactly) If I put this, I get a rounding error: =(a3-a2-a1) (gives 1.42e-14) In another spreadsheet, the error was much larger (2e-9). Excel should give the same result either way, but it doesn't. Does anyone know why? Jason |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding error is conditional on brackets
A very common question. It is to do with the way Excel stores
fractional values, as most decimal fractions cannot be represented accurately in binary. Take a simple decimal example, using 2 dp: x = 1/3 = 0.33 x + x + x = 0.33 + 0.33 + 0.33 = 0.99 which is not equal to 1. Pete On Feb 4, 5:08*pm, JasonG wrote: If I put these values and formulae, I get a zero total, which is what I expect a1: 123.456 a2: 234.567 a3: 358.023 =a3-a2-a1 * *(gives zero exactly) If I put this, I get a rounding error: =(a3-a2-a1) * (gives 1.42e-14) In another spreadsheet, the error was much larger (2e-9). Excel should give the same result either way, but it doesn't. Does anyone know why? Jason |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding error is conditional on brackets
Hi Jason,
Look he http://support.microsoft.com/kb/78113 -- Kind regards, Niek Otten Microsoft MVP - Excel "JasonG" wrote in message ... | If I put these values and formulae, I get a zero total, which is what I expect | | a1: 123.456 | a2: 234.567 | a3: 358.023 | | =a3-a2-a1 (gives zero exactly) | | If I put this, I get a rounding error: | | =(a3-a2-a1) (gives 1.42e-14) | | In another spreadsheet, the error was much larger (2e-9). | | Excel should give the same result either way, but it doesn't. | | Does anyone know why? | | Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Rounding | Excel Discussion (Misc queries) | |||
Conditional Sum Brackets { } | Excel Worksheet Functions | |||
conditional rounding | Excel Worksheet Functions | |||
Conditional Rounding | Excel Worksheet Functions | |||
Percentage rounding error in charts | Excel Discussion (Misc queries) |