|
I've been noticing something strange going on with Excel 2007. Sometimes, when I have a column of numbers set to the Accounting format, when I sum them in a new cell I get a wrong amount. For example, I'll see something like this: code:
Is there a fix, other than to use the ROUND functions or the display precision option?
|
# ¿ Jul 29, 2015 16:22 |
|
|
# ¿ Apr 28, 2024 15:31 |
|
Alereon posted:Are you actually seeing this with the example you gave? Because floating point precision issues should only be affecting really small numbers, or causing a very small difference from what you expect, nothing like cents out of ten dollars. And yes, calculators have the exact same issue, there are certain math operations you can do to reveal prevision limitations. Not with those specific numbers. The ones I deal with are in the high hundreds and low thousands, but I didn't want to put the exact ones because they're payroll numbers.
|
# ¿ Jul 29, 2015 17:45 |
|
sarehu posted: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. 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? The errors are happening in a column whose sum is less than $40,000.
|
# ¿ Jul 29, 2015 18:14 |
|
Found the problem, but it makes no sense. This column: 633.50 is the correct amount (manually adding the column on a calculator), 633.48 is the amount from using SUM. The numbers in this column (aside from the sum) were copied/pasted from another worksheet in the same spreadsheet. When I delete the 44.55 (2nd row) and enter it manually, the 633.48 changes to 633.49. When I delete the first 159.48 (8th row) and enter it manually, the 633.49 changes to 633.50, and now everything adds up correctly. I guess that means I enter everything manually from now on, but how does a copy/paste actually change a value?
|
# ¿ Jul 29, 2015 19:16 |
|
sarehu posted: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. I had no idea. Thanks for the info!
|
# ¿ Jul 29, 2015 21:10 |
|
|
# ¿ Apr 28, 2024 15:31 |
|
Yes - I have the cells format set to "accounting".
|
# ¿ Aug 1, 2015 13:50 |