Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
I made a little calculation sheet at work and it's out 2 cents. I
figure it's because of the forumulas in the bottom part where the calculations of n/180 share. Any idea how I can make the sheet at least clue me in that it's not accurate? I cut and pasted the sheet below. I don't know how to attach it so you can see my calculations. They are simple adding and subtracting except for the share calculations, such as =D33/180*27 Thanks for any suggestions. RECEIPTS Public Guardian and Trustee 1,563,000.00 Government of Canada €“ tax refund 2,223.92 Public Guardian and Trustee - balance of funds 3,729.21 Vancouver Coastal Health 320.33 Total receipts 1,569,273.46 DISBURSEMENTS BC SPCA €“ specific bequest 15,000.00 Salvation Army €“ specific bequest 15,000.00 Law LLP €“ account Dec. 15, 2011 8,925.35 Total disbursements 38,925.35 Balance remaining held in trust 1,530,348.11 PROPOSED €“ HOLDBACK & DISBURSEMENTS Holdback to cover final Canadian taxes, Legal and accounting fees 100,000.00 Holdback to cover UK taxes and Accounting fees Nil * (No UK Taxes payable as per advice the Administratrix received from her English accountant. No advice given on this matter by Law) Administrators Fees at 1% 15,692.73 BALANCE AVAILABLE FOR INTERIM DISTRIBUTION 1,414,655.38 PROPOSED INTERIM DISTRIBUTION Mary (90/180 share) 707,327.69 Jean (27/180 share) 212,198.31 David (27/180 share) 212,198.31 Pat (4/180 share) 31,436.79 John (4/180 share) 31,436.79 Peter (4/180 share) 31,436.79 John B (4/180 share) 31,436.79 Frederick (4/180 share) 31,436.79 William (8/180 share) 62,873.57 James (8/180 share) 62,873.57 Total interim distribution: 1,414,655.38 (All funds in Canadian Dollars) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
"Diane Van" wrote:
I made a little calculation sheet at work and it's out 2 cents. I figure it's because of the forumulas in the bottom part where the calculations of n/180 share. Absolutely! Unless 1,414,655.38*n is divisible by 180, we would expect rounding problems when dividing. You might have calculated ROUND(1,414,655.38*n/180,2) [1]. If, instead, you calculate 1,414,655.38*n/180 and use a numeric format with 6 decimal places, you will see the problem. (Forgive me if the columns do not align properly.) Mary 90 707,327.690000 Jean 27 212,198.307000 David 27 212,198.307000 Pat 4 31,436.786222 John 4 31,436.786222 Peter 4 31,436.786222 JohnB 4 31,436.786222 Frederick 4 31,436.786222 William 8 62,873.572444 James 8 62,873.572444 180 1,414,655.380000 There are many ways to ameliorate the rounding problem. None is perfect. Arguably, the best methods are implemented in a VBA procedure [3]. But most people don't want the hassles of dealing with VBA code. The simplest method is to change only the last formula. Assume the data above are in columns A through C and rows 1 through 11. The formula in C1 through C9 might be of the form (C1 by example): =ROUND($C$11*B1/$B$11,2) The formula in C10 would be [2]: =ROUND($C$11-SUM(C1:C9),2) However, that penalizes the last person on the list, subtracting the sum of all of the rounding errors. Alternatively, combine that method with the following: C1: =ROUND($C$11*B1/$B$11,2) C2: =ROUND($C$11*SUM($B$1:B2)/$B$11-SUM($C$1:C1),2) Copy C2 into C3 through C9 C10: =ROUND($C$11-SUM(C1:C9),2) That tends to distribute the rounding "error", especially as the number of lines (people) increases. Does that help? Questions? ----- [1] Or you set the "Precision as displayed" (PAD) calculation option and formatted the cell with 2 decimal places. I will use explicit rounding. PAD obviates the need for explicit rounding in many instances. Nevertheless, I do not recommend PAD for many reasons. [2] I round all formulas that involve non-integers to avoid infinitesimal numerical "errors" (anomalies, not defects) due to the way that Excel stores numbers. It is "good practice", IMHO. But not everyone wants to bother with it -- unless they get bitten ;-). [3] There is a good implementation somewhere. But I cannot find it at the moment. I will keep searching for it. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
Make the amount for Jean and David... 212,198.30 and continue on..
It's difficult to dole out parts of a penny. -- Jim Cone Portland, Oregon USA http://www.contextures.com/excel-sort-addin.html editorial review of special sort excel add-in (30 ways to sort) "Diane Van" wrote in message news:2012030519335094544-DianeVan@somemailcom... I made a little calculation sheet at work and it's out 2 cents. I figure it's because of the forumulas in the bottom part where the calculations of n/180 share. Any idea how I can make the sheet at least clue me in that it's not accurate? I cut and pasted the sheet below. I don't know how to attach it so you can see my calculations. They are simple adding and subtracting except for the share calculations, such as =D33/180*27 Thanks for any suggestions. -snip- PROPOSED INTERIM DISTRIBUTION Mary (90/180 share) 707,327.69 Jean (27/180 share) 212,198.31 David (27/180 share) 212,198.31 Pat (4/180 share) 31,436.79 John (4/180 share) 31,436.79 Peter (4/180 share) 31,436.79 John B (4/180 share) 31,436.79 Frederick (4/180 share) 31,436.79 William (8/180 share) 62,873.57 James (8/180 share) 62,873.57 Total interim distribution: 1,414,655.38 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
PS.... I wrote:
Arguably, the best methods are implemented in a VBA procedure [3]. But most people don't want the hassles of dealing with VBA code. [....] [3] There is a good implementation somewhere. But I cannot find it at the moment. I will keep searching for it. Found it! Refer to http://www.sulprobil.com/html/largest_remainder.html by Bernd Plumhoff. The problem with Bernd's website: I find it very difficult to use. But if you download his example Excel file, you can usually figure it out -- with some effort. Also note that Bernd has a link to an implementation of another algorithm, the d'Hondt method. In addition to Bernd's information, refer to http://h2g2.com/dna/h2g2/A2757873. IMHO, while these might do a better job of distributing rounding error fairly, I still stick with the methods that I described previously, if only because they are relatively easy to understand and implement, and their results are usually "good enough". |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
Thanks both for all the suggestions.
I thought I would be smart and use Excel instead of an adding machine (yes with a tape and everything) and was surprised that the result when printed or viewed on screen was incorrect. After I paid all those folks I had only 99,999.98 left not $100,000, which is a little alarming. So I guess my main question is in the future how I can make such calculations and rely on the results or at least see there is a problem. I guess I could just put on several decimal points when calculating and then could 'see' the issue. I like the rounding too and will try that in the future. I don't often have this kind of calcuation and I'm not that good at Excel Thanks for all your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
"Diane Van" wrote:
I thought I would be smart and use Excel instead of an adding machine (yes with a tape and everything) Same thing happens on an adding machine. The only difference is: when you notice the problem with an adding machine, you probably just fudge the numbers instinctively. "Diane Van" wrote: So I guess my main question is in the future how I can make such calculations and rely on the results or at least see there is a problem. I guess I could just put on several decimal points when calculating and then could 'see' the issue. I like the rounding too and will try that in the future. Explicit rounding alone does not solve the problem. In a sense, it creates the problem. Rounding error of this nature -- arising because you cannot divide parts (it is a called quantization error) -- is very common. Look at any professional financial report where percentages are calculated, and you should see a footnote that explains that columns might not add up to their totals due to rounding. There are several ways to deal with this in the future. First, you can simply accept the fact that you cannot divide 2 cars between 3 people. In other words, do the best you can, and do not worry about the effects of rounding as long as the column adds up. But for that to work, you cannot use the "Precision as displayed" calculation option. (Or you must use the General format, which is probably not acceptable.) Second, follow either or both of the procedures that I described or use another method to try distribute the rounding error due to quantization. Third, you can detect the error by adding a Conditional Format (or some other method) to compare the sum of the column with the expected total and highlight a difference. One caution about comparison, however: infinitesimal differences can arise due to the way that Excel stores numbers and performs arithmetic. And that can cause false negatives. This kind of rounding error is different from most calculators. For example, =IF(10.1-10=0.1,TRUE) returns FALSE(!). The remedy is due to be diligent about rounding arithmetic with non-integers, even simple adding and subtracting, when you expect the result to be accurate to a specific precision, for example dollar and cents. For example, =IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE. This kind of rounding error is unfortunate. People blame Excel; and it is true that there are things that Excel could do to minimize it. But the fact is: Excel is simply relying on the computer arithmetic hardware, which in turn relies on an industry-standard way to represent numbers. In fact, most software will have the same problem. I mention that only to make it clear that Microsoft is not likely to "fix" this issue any time soon. So we must work around it ourselves by the diligent use of explicit rounding. I hope that helps you move forward. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
On Tue, 6 Mar 2012 05:20:06 -0800, Diane Van
wrote: Thanks both for all the suggestions. I thought I would be smart and use Excel instead of an adding machine (yes with a tape and everything) and was surprised that the result when printed or viewed on screen was incorrect. After I paid all those folks I had only 99,999.98 left not $100,000, which is a little alarming. So I guess my main question is in the future how I can make such calculations and rely on the results or at least see there is a problem. I guess I could just put on several decimal points when calculating and then could 'see' the issue. I like the rounding too and will try that in the future. I don't often have this kind of calcuation and I'm not that good at Excel The other method is to truncate all outgoings to two (dollar0 decimal places - the 1c level, and accumulate all these fractional cents back into the account, with a footnote explaining the action taken. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
out 2 cents - auto rounding?
Thanks for all your help.
Diane On 2012-03-07 01:33:18 +0000, who where said: On Tue, 6 Mar 2012 05:20:06 -0800, Diane Van wrote: Thanks both for all the suggestions. I thought I would be smart and use Excel instead of an adding machine (yes with a tape and everything) and was surprised that the result when printed or viewed on screen was incorrect. After I paid all those folks I had only 99,999.98 left not $100,000, which is a little alarming. So I guess my main question is in the future how I can make such calculations and rely on the results or at least see there is a problem. I guess I could just put on several decimal points when calculating and then could 'see' the issue. I like the rounding too and will try that in the future. I don't often have this kind of calcuation and I'm not that good at Excel The other method is to truncate all outgoings to two (dollar0 decimal places - the 1c level, and accumulate all these fractional cents back into the account, with a footnote explaining the action taken. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding to the nearest 50 cents | Excel Discussion (Misc queries) | |||
Currency Rounding to nearest 5 cents | Excel Discussion (Misc queries) | |||
Rounding to nearest 5 cents in excel | Excel Worksheet Functions | |||
rounding to closest 5 cents | Excel Worksheet Functions | |||
rounding to 5 cents | Excel Programming |