Trying to calculate distribution percentages - dealing with rounding errors

%3CLINGO-SUB%20id%3D%22lingo-sub-3544412%22%20slang%3D%22en-US%22%3ETrying%20to%20calculate%20distribution%20percentages%20-%20dealing%20with%20rounding%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3544412%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20with%20my%20formulas.%26nbsp%3B%20I%20have%20large%20dollar%20amounts%20that%20are%20being%20distributed%20into%20small%20percentages%20such%20as%201.3636363636.%26nbsp%3B%20As%20an%20example%2C%20if%20I%20am%20distributing%20%24750%2C000%2C%20I%20will%20end%20up%20with%20an%20amount%20such%20as%2010%2C227.27272727%20which%20when%20changing%20the%20column%20to%20dollars%20is%20rounded%20to%20%2410%2C227.28.%26nbsp%3B%20The%20problem%20is%20the%20totals%20then%20don't%20add%20up%20to%20%24750%2C000%20(usually%20a%20.35%20to%20.60%20difference%20and%20the%20corresponding%20columns%20are%20all%20off.%26nbsp%3B%20%26nbsp%3BThe%20formula%20I%20am%20using%20is%20%3D750000*D1%25%26nbsp%3B%20(D%20column%20equals%20member%20percentage).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20any%20help%20--%20I'm%20an%20%22old%22%20lady%20who%20has%20just%20been%20trying%20to%20teach%20myself%20Excel%20(I%20learned%20to%20type%20of%20a%20manual%20typewriter%20and%20used%20a%2010-key%20calculator%20for%20years).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3544412%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3545364%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20calculate%20distribution%20percentages%20-%20dealing%20with%20rounding%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3545364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1434209%22%20target%3D%22_blank%22%3E%40Diana_Mullenyahoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERounding%20%22errors%22%20of%20this%20sort%20are%20very%20common%20because%20%22the%20sum%20of%20the%20rounded%20parts%20might%20not%20equal%20the%20whole%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOften%2C%20they%20are%20simply%20tolerated.%26nbsp%3B%20Most%20financial%20reports%20contain%20a%20footnote%20to%20the%20effect%20of%20%60%60numbers%20might%20not%20add%20up%20due%20to%20rounding%60%60.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20remedy%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20P1%20(first%20part)%3A%26nbsp%3B%20%3DROUND(T1*D1%25%2C%200)%3C%2FP%3E%3CP%3EIn%20P2%3AP100%20(other%20parts)%3A%26nbsp%3B%20%3DROUND(%24T%241*SUM(%24D%241%3AD2)%25%20-%20SUM(%24P%241%3AP1)%2C%200)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECaveat....%26nbsp%3B%20Then%20you%20might%20notice%20an%20off-by-one%20error%20for%20some%20values%20in%20P2%3AP100.%26nbsp%3B%20That%20is%20a%20necessary%20consequence.%26nbsp%3B%20So%2C%20I%20would%20still%20have%20the%20footnote%20(generalized)%3A%26nbsp%3B%20%60%60calculated%20numbers%20might%20differ%20due%20to%20rounding%60%60.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1434209%22%20target%3D%22_blank%22%3E%40Diana_Mullenyahoo%3C%2FA%3E%26nbsp%3B%20wrote%3A%26nbsp%3B%20%60%60trying%20to%20teach%20myself%20Excel%20%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20I%20commend%20you%20for%20learning%20that%20we%20can%20write%20D1%25%2C%20where%20D1%20contains%20a%20decimal%20presentation%20of%20a%20percentage%20(e.g.%201.23%20for%201.23%25).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20people%20do%20not%20know%20that%20%22%25%22%20is%20an%20operator%20that%20means%20%22divide%20by%20100%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%2C%20I%20would%20recommend%20that%20you%20have%20actual%20percentages%20in%20D1%20et%20al.%26nbsp%3B%20For%20example%2C%201.3636363636%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3E%25%3C%2FFONT%3E%3C%2FSTRONG%3E%20instead%20of%201.3636363636.%26nbsp%3B%20Simply%20format%20D1%20as%20Percentage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20can%20write%20simply%20D1%20instead%20of%20D1%25%20everywhere.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20that%20probably%20simplifies%20the%20calculation%20in%20D1%2C%20which%20I%20presume%20is%20of%20the%20form%20%3D100*A1%2F%24B%241%2C%20where%20B1%20is%20the%20sum%20of%20A1%3AA100%2C%20for%20example.%26nbsp%3B%20The%20formula%20can%20be%20simply%20%3DA1%2F%24B%241%2C%20formatted%20as%20Percentage.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.