out 2 cents - auto rounding?

  • Thread starter Thread starter Diane Van
  • Start date Start date
D

Diane Van

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)





Administrator’s 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)
 
Diane Van said:
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.
Click to expand...

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.
 
PS.... I said:
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.
Click to expand...

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".
 
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.
 
Diane Van said:
I thought I would be smart and use Excel instead of an
adding machine (yes with a tape and everything)
Click to expand...

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 said:
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.
Click to expand...

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.
 
Thanks for all your help.

Diane








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.
Click to expand...
 
Back
Top