Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Locked thread
Shroud
May 11, 2009
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:
Col. A     Col. B

Cell 1      1.01
Cell 2      2.02
Cell 3      3.03
Cell 4      4.04
Cell 5      5.05

Total      15.17 (formula - sum(B3:B7))
I've googled and seen Microsoft say it's a result of floating point stuff (https://support.microsoft.com/en-us/kb/214118), but don't calculators do the same thing while getting the correct result?

Is there a fix, other than to use the ROUND functions or the display precision option?

Adbot
ADBOT LOVES YOU

Alereon
Feb 6, 2004

Dehumanize yourself and face to Trumpshed
College Slice
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.

Shroud
May 11, 2009

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.

sarehu
Apr 20, 2007

(call/cc call/cc)
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).

Shroud
May 11, 2009

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.

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).

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.

sarehu
Apr 20, 2007

(call/cc call/cc)

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?

The errors are happening in a column whose sum is less than $40,000.

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.

Shroud
May 11, 2009
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?

sarehu
Apr 20, 2007

(call/cc call/cc)
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.

Shroud
May 11, 2009

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!

Bob Morales
Aug 18, 2006


Just wear the fucking mask, Bob

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

Store all your numbers as pennies!

then / 100 to get your $ amount

~Coxy
Dec 9, 2003

R.I.P. Inter-OS Sass - b.2000AD d.2003AD
Are you absolutely sure that the values are all 2DP and not just being displayed that way?
That could be one reason why you get different results when you delete a cell and type it in manually.

Shroud
May 11, 2009
Yes - I have the cells format set to "accounting".

r u ready to WALK
Sep 29, 2001

You're supposed to pocket those fractional errors for yourself dude
https://www.youtube.com/watch?v=1pl03dzR524

SirViver
Oct 22, 2008

Shroud posted:

Yes - I have the cells format set to "accounting".
Setting a cell format does not round the underlying values. All the accounting format does is show the content rounded to two decimal places and the currency symbol on the left. You can enter 1.235 and Excel will display it as 1.24, but the actual value stored in the cell is still 1.235. If you then sum multiple cells like this Excel will calculate the most accurate result it can using the actual non-rounded/non-display-formatted values.

If you need to get the sum of the rounded values (because for example in reality you can't pay someone half a cent), then adjust your formula like so:
SUM(ROUND(B3:B7, 2))

Adbot
ADBOT LOVES YOU

sarehu
Apr 20, 2007

(call/cc call/cc)
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.

  • Locked thread