Trying to calculate distribution percentages - dealing with rounding errors

Occasional Visitor

I am struggling with my formulas.  I have large dollar amounts that are being distributed into small percentages such as 1.3636363636.  As an example, if I am distributing $750,000, I will end up with an amount such as 10,227.27272727 which when changing the column to dollars is rounded to $10,227.28.  The problem is the totals then don't add up to $750,000 (usually a .35 to .60 difference and the corresponding columns are all off.   The formula I am using is =750000*D1%  (D column equals member percentage).  

 

I appreciate any help -- I'm an "old" lady who has just been trying to teach myself Excel (I learned to type of a manual typewriter and used a 10-key calculator for years).  

1 Reply

@Diana_Mullenyahoo 

 

Rounding "errors" of this sort are very common because "the sum of the rounded parts might not equal the whole".

 

Often, they are simply tolerated.  Most financial reports contain a footnote to the effect of ``numbers might not add up due to rounding``.

 

One remedy is:

 

In P1 (first part):  =ROUND(T1*D1%, 0)

In P2:P100 (other parts):  =ROUND($T$1*SUM($D$1:D2)% - SUM($P$1:P1), 0)

 

Caveat....  Then you might notice an off-by-one error for some values in P2:P100.  That is a necessary consequence.  So, I would still have the footnote (generalized):  ``calculated numbers might differ due to rounding``.

 

-----

 

@Diana_Mullenyahoo  wrote:  ``trying to teach myself Excel ``

 

First, I commend you for learning that we can write D1%, where D1 contains a decimal presentation of a percentage (e.g. 1.23 for 1.23%).

 

Most people do not know that "%" is an operator that means "divide by 100".

 

Nevertheless, I would recommend that you have actual percentages in D1 et al.  For example, 1.3636363636% instead of 1.3636363636.  Simply format D1 as Percentage.

 

Then you can write simply D1 instead of D1% everywhere.

 

Also, that probably simplifies the calculation in D1, which I presume is of the form =100*A1/$B$1, where B1 is the sum of A1:A100, for example.  The formula can be simply =A1/$B$1, formatted as Percentage.