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>
