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
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
