Okay, I know it is a sin, but given the length of the post it might be easier to top post..
Might I suggest that rather than rounding each row across and then summing the last column at the bottom which gives the erroneous result, that you not sum the column in the last row, but sum the first column and then round it to two places and display it in the third column. I.e, 1st column Sum=8.368 -> (round) -> 8.37 which displays in the total in the third column. Rounding (and some other math functions) is always problematic and often introduces errors as it is only an approximation. Yeah, in bookkeeping and billing you have to watch for this, But even the IRS has given up on the pennies and asks that one round to dollars BEFORE entering on the line, and it does create errors where you might pay a few cents more or less taxes. They gave up, I guess, on trying to get people to do it all the same way and creating audit costs. In the long run it doesn't matter, in the short run for an invoice it just looks like you can't add. ;-> Allen On Tue, 9 Mar 2010 14:44:35 -0500, "Twayne" <[email protected]> wrote: > OK, I see the point your're trying to make and understand why you did it > as > you did. It's a little Hogan's Barn-ish, but - your point came thru this > time. Actually, when anyone uses more than two decimal places it has to be > shown as sucn on the invoice so the recip doesn't need to wonder how it > was > done. Mathematically, your presentation is a poor one IMO and I'd never > use > such a construct on an invoice. Accounts payable should never had to guess > how something was done or what presicion was used, which is the crux of > all > this. If the math is consistant, then the result will be consistant. The > only rounding really necessary is to make the bill meet currency format. > FWIW, Excel does everything the same way; exact same results. > > Regards, & thanks > > Twayne` > > > > In news:[email protected], > Harold Fuchs <[email protected]> typed: >> 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> > > > > -- --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
