Daniel Lewis wrote:
Harold Fuchs wrote:
Daniel Lewis wrote:
Harold Fuchs wrote:
Twayne wrote:
<snip>
I managed to create a tiny spreadsheet that exhibits the
problem: 1.456 1.46 1.46
2.345 2.35 2.35
4.567 4.57 4.57
8.368 8.37 8.38
The first column shows how I entered the data [the last row
shows the result of =sum(..)]; the second row shows how it
and the =sum(...) appears under default (2 decimal places)
formatting; the third shows the result of the procedure I
describe below.
Please try the following, assuming your values are in
column A and that column B is spare - adjust as appropriate:
1. in B1 enter the formula =INT((A1+0.005)*100)/100
2. copy B1 down column B for as many rows as are
necessary to cover all the values
3. in the *next* row in column B enter the formula
=SUM(B1:Bn) where "n" is the number of values
4. compare this result to your previous one - the sum of
column A.
The formula in #1 above is designed to remove any rounding
errors in column A and put the result in column B. If the
sum in column B is the same as that in column A and if that
sum is wrong, then I'd say you've found a bug :-(
Harold,
Haven't been in a classroom in a lonnnnnggg time, but I fail to
see the significance of adding 0.005 to each manually user entered
number in the leftmost column to get column B. It is of course,
going to make any digits after the decimal point round upwards,
even when it shouldn't: e.g. 123.111 becomes 123.116 or, if one
exposes one more decimal places, 123.1160. That's incorrect, as
rounding will either leave a number as is, or increase it by 1 at
the relevant decimal place. 123.111 should round to 123.11 or
123.1 or 123, but never 123.116 or 123.17 because that creates
only a round-up. Each additional entry will then increase the
summation errors very substantially with a list of more than a few
digits to add up.
123.111 will round to 123.11 as you say, but 123.115 will round to
123.12 which is what we want. After rounding, the formula
multiplies by 100, giving 12311.1 and 12312.0 respectively; the INT
then gives 12311 and 12312 respectively and then dividing by 100
gives the correct results. The idea is to make the 3rd decimal
place have its correct effect.
Perhaps you can enlighten me as to the specific point you're
trying to make with the second column where you add 0.005 to each
manual entry? 1.4560 1.4600 1.46 2.3450 2.3500
2.35 4.5670 4.5700 4.57 8.3680 8.3800 8.37
Sums in column B is incorrect for what was entered, but not
incorrect for the data Calc would use. 4 Dec Plcs 4 Dec Plcs
Ea +.005 2 Dec Plcs
The second column shows how Calc displays the values. The display
for rows 1-3 are correct to 2 decimal places but the 4th row, the
=SUM(...) is **wrong**. If you add up the *displayed* values you do
*not* get the displayed result !!! The reason is that if you add
1.456, 2.345 and 4.567 you get 8.368 which rounds to 8.37.
The third column shows the results of my formula and the
corresponding =SUM(...) . The displayed values are the same but now
the sum is correct.
1.456 1.46 1.456 2.345 2.35
2.345 4.567 4.57 4.567 8.368 8.38 8.37
Sums. Again, column B is no longer the same as A. Sum
(a7:a9) sum(b7:b9) Sum a7:a9
Exactly. Same problem. You need to round *before* you add; Calc
adds before rounding *because* it uses the more precise values in
its calculation. Calc (correctly) does not use the displayed
values. Asking for a display of 2 decimal places doesn't change the
underlying value, only its appearance.
Ea + .005 in middle column
First Column, all 3 dec places, same as yours; typed numbers and
summing.
Second Column, 4 - 2 decimal places resp top and bottom, using
your formula and summing.
Third Column, 2 - 3 dec places resp, top to bottom, and summed.
Summing the numbers in Calc or Excel, 8.368 sum for column 1 sums
correctly to rounded off 8.37 in Column 3, for a 2 dec place
summation.
You also said:
" ...#1 above is designed to remove any rounding
errors in column A and put the result in column B...
"
But I fail to see the relevence of it. Perhaps I'm blind but I
also don't see a "#1", but I think I know what you meant.
I gave a numbered list of 3 items. It is common practice to refer
to these separately as #1, #2 and #3. The # sign is pronounced
"number" in all the business contexts I've ever encountered.
Trouble is, adding 0.005 to each number is going to force each
number to round to the next higher, whether it should round
upwards or not. If you start with 1 and add 5, =6, rounding off
at that decimal place is going to happen. Same for 0 thru 4 and
even does a rollover at 5 and above. How can that possibly be
intended to account for rounding errors in Column A? What it
really does is make the more like integers than rounded numbers.
Whatever precision the numbers are typed in as is the precision
used in calculations, regardless of how many digits (and resulting
rounding) you choose to have display.
The point is you don't want to use the precise values for the
addition. You want to use the rounded values. Otherwise you get the
ludicrous effect shown in my column 2 where 1.46, 2.35 and 4.57 add
to 8.37 which is clearly wrong and arises exactly because you are
adding the more precise values.
The difference between my columns 2 and 3 is that in column 2 the
actual values and the displayed values are different. In column 3
they are the same. They have been forced to be the same by rounding
each row separately.
1.5 1.5 1.5 2.3 2.4 2.3 4.6 4.6 4.6 8.4
8.4 8.4 Note that, with 3 or more decimal
places, the numbers as originally entered are still used in the
calculation. One simply has to be careful to consider the effects
of rounding vs # digits to display. Other than the apparent
error by adding 0.005 to each entry, the sums are indeed the same.
Excel treats these numbers the same way. What did I miss in the
point you wanted to make?
The point I want to make is that if you want your invoices to
*appear* to add up correctly you need to round each item before
using it in the total. An invoice showing 1.46, 2.35 and 4.57
adding to 8.37 is ridiculous.
<snip>
One of the problems is that INT((A1+.005)*100)/100 is not doing
what you want it to do. You are introducing an error which depends
upon the number of entries whose last digit is 5 or more. You need a
minimum of 3 numbers whose last digit is at least 5. This causes the
sum of the INT((A1+.005)*100)/100 to be .01 more than it would be if
you were adding the numbers using two decimal places. It appears
that the error happens when there are an odd quantity of numbers
whose last digit is 5 thought 9. For an even quantity of these
numbers, INT((A1+.005)*100) will round half of the numbers lower and
half higher. Thus the sums are the same to 2 decimal places. *BUT*,
when the quantity of these numbers is odd, there is one more number
being rounded up than being rounded down. Hence, that one number
rounded up produces the error.
Dan
I don't think this is right. The following shows the same error even
though there are 4 values whose third digit is 5 or more:
1.234 1.23 1.23
2.345 2.35 2.35
3.456 3.46 3.46
4.567 4.57 4.57
5.678 5.68 5.68
17.280 17.28 17.29
Again, the first column (rows 1-5) is as I entered the data; the
second is as Calc displays the values with 2dp set; the third is
after the application of my original formula (note that I have
deliberately not used the better formula offered by Franz Wein's
e-mail in this thread so as not to confuse things with discussions
about whether or not his formula is equivalent to mine). Again the
sum in column 2 (row 6) is *wrong* if you add the displayed values
while that in column 3 is right.
I don't think the number of values has anything to do with the
principle that if the *displayed* total is always to be the correct
sum of the *displayed* values then those values must be rounded
before the addition is performed. I learnt this principle when I used
my first spreadsheet application in the mid 1970's (yes, really) and
I don't think it's changed - because it's fundamental to the way
arithmetic works. Ask an accountant.
I, personally doubt this principle. Anytime rounding is done, an error
is introduced. In the 1956-1960 school years, I learned this. To
reduce the error, here is what we were taught. If 5 is the final
digit, look at the digit to its left. If that digit is even, then
round down. If that digit is odd, then round up. So, 2.345 becomes
2.34. 2.355 becomes 2.36. In long columns of numbers being added, the
probability is that approximately half of the numbers ending in 5
will have an even digit to its left and the other half of the numbers
have an odd digit preceding the final 5.
Here is why I doubt the principle you stated: I took the same numbers
as you did and changed all the final digits to a 5 if the original
final digit was greater than 5. Rather interesting results. Rounding
results in a difference of 2 when rounding to
1.234 1.23 1.23 two decimal places.
2.345 2.35 2.35
3.455 3.46 3.46
4.565 4.57 4.57
5.675 5.68 5.68
17.274 17.27 17.29
What is the problem? Why is one sum 17.27 and the other 17.29? Simple
answer: the first sum was rounded after adding the numbers; the second
sum came from rounding the numbers first and adding them afterward. In
math lingo, These two operations are not commutative. The results
depend directly on the which order the operations are performed.
Exactly. As you say "The results depend directly on the which order the
operations are performed. "
Just look at your own numbers. The sum in column 2 (17.27) is patently
*not* the sum of the values in column 2 whereas the sum in column 3 *is*
the sum of the values in column 3. This is precisely my point.
If the numbers in column 1 represented monetary amounts based on some
calculation using numbers not shown, and if you had to pay each value
*separately*, the total of your outgoings would be as shown in column 3,
*not* as shown in column 2. That's because each of your payments would
have been rounded according to my (or Franz Wein's) formula.
In fact, the *method* used to round the individual numbers is not the
issue. The point is that the rounding must be done before the addition.
If not, then the "printed" numbers may well not add to the "printed" total.
As an aside, Google shows me that the odd/even method of rounding you
describe is sometimes called "banker's rounding". All I can say is that
I have spent a long time (since the mid '70s) helping major banks all
over the world implement computerised spreadsheets and have never been
asked for this method nor seen it used either on computers or on
calculators. Google also shows me that "Banker's rounding" is not available
* on any HP or Casio calculator
* as a built-in function in OpenOffice.org Calc
* as a built-in function in Microsoft Excel, although it is
available in Microsoft Visual Basic for Applications (VBA).
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
