|
If they're using IEEE 754, the numbers you input, if they have fractional parts, have an inexact representation with about 15 or 16 decimal digits of precision. If they're in the high hundreds or low thousands, you're looking at about 42 binary digits right of the decimal place, 36 binary digits right of the cents place. The maximum error caused by rounding to the nearest binary representation on an initial value you've input would be about 2^-43, if it's between 1024 and 2048. How much rounding error you could get by computations combining these values depends on what computations are done, and also on how Excel carries them out. (There's more than one way to add a list of numbers and they have different characteristics about how much error could possibly accumulate, in terms of maximum error and expected value.) This is not something you'd see affecting results with 5 numbers or 1000 numbers. I think it's quite likely that you're truncating a value somewhere it should be rounded, or something like that. If you want to eliminate errors when adding dollars-and-cents values, you can operate in terms of cents. Multiply by 100, round to the nearest integer, add the numbers, divide by 100. There is no loss of precision when working with integers (unless they're more than 2^53).
|
#
¿
Jul 29, 2015 18:05
|
|
|
|
| # ¿ Apr 19, 2024 06:13 |
|
|
Shroud posted:Would it be affected by the fact that I use the ROUNDUP function in previous cells that are then summed, or that some cells are the result of a copy/paste? I don't know about the copy/pasting, but if a cell A1 ends up with a value such as 1.180000000001, then ROUNDUP(A1, 2) will evaluate to 1.19. Likewise, ROUNDUP(1.000000001) will produce 2. You might want to consider whether some precision error is putting the function's input on the wrong side of its jump discontinuity, and look "closely" to see what's happening.
|
|
#
¿
Jul 29, 2015 18:51
|
|
|
It's displaying rounded values. When you type in "44.55" manually, you're replacing a value that's quite far from 44.55 with the closest possible representation.
|
|
#
¿
Jul 29, 2015 20:55
|
|
|
Typically you'll want to round the result of sums, to wipe out the error, instead of rounding the cells at every place that uses them, only to add them together and get little floating point errors all over again. But in particular you'll want to round before a function that's discontinuous right at the 1-cent boundary, such as ROUNDUP.
|
|
#
¿
Aug 3, 2015 05:30
|
|