Forum Discussion

Diana_Mullenyahoo's avatar
Diana_Mullenyahoo
Copper Contributor
Jun 23, 2022

Trying to calculate distribution percentages - dealing with rounding errors

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).  

excel
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Jun 23, 2022

    Diana_Mullenyahoo 

     

    Rounding "errors" of this sort are very common because "the sum of the rounded parts might not equal the rounded 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.

Resources