Discussion Re: Trying to calculate distribution percentages - dealing with rounding errors in Excel
https://techcommunity.microsoft.com/t5/excel/trying-to-calculate-distribution-percentages-dealing-with/m-p/3545364#M151616
<P><LI-USER uid="1434209"></LI-USER> </P><P> </P><P>Rounding "errors" of this sort are very common because "the sum of the rounded parts might not equal the whole".</P><P> </P><P>Often, they are simply tolerated. Most financial reports contain a footnote to the effect of ``numbers might not add up due to rounding``.</P><P> </P><P>One remedy is:</P><P> </P><P>In P1 (first part): =ROUND(T1*D1%, 0)</P><P>In P2:P100 (other parts): =ROUND($T$1*SUM($D$1:D2)% - SUM($P$1:P1), 0)</P><P> </P><P>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``.</P><P> </P><P>-----</P><P> </P><P><LI-USER uid="1434209"></LI-USER> wrote: ``trying to teach myself Excel ``</P><P> </P><P>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%).</P><P> </P><P>Most people do not know that "%" is an operator that means "divide by 100".</P><P> </P><P>Nevertheless, I would recommend that you have actual percentages in D1 et al. For example, 1.3636363636<STRONG><FONT color="#DF0000">%</FONT></STRONG> instead of 1.3636363636. Simply format D1 as Percentage.</P><P> </P><P>Then you can write simply D1 instead of D1% everywhere.</P><P> </P><P>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.</P>Thu, 23 Jun 2022 20:12:03 GMTJoe User2022-06-23T20:12:03ZTrying to calculate distribution percentages - dealing with rounding errors
https://techcommunity.microsoft.com/t5/excel/trying-to-calculate-distribution-percentages-dealing-with/m-p/3544412#M151584
<P>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). </P><P> </P><P>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). </P>Thu, 23 Jun 2022 15:47:01 GMThttps://techcommunity.microsoft.com/t5/excel/trying-to-calculate-distribution-percentages-dealing-with/m-p/3544412#M151584Diana_Mullenyahoo2022-06-23T15:47:01ZRe: Trying to calculate distribution percentages - dealing with rounding errors
https://techcommunity.microsoft.com/t5/excel/trying-to-calculate-distribution-percentages-dealing-with/m-p/3545364#M151616
<P><LI-USER uid="1434209"></LI-USER> </P><P> </P><P>Rounding "errors" of this sort are very common because "the sum of the rounded parts might not equal the whole".</P><P> </P><P>Often, they are simply tolerated. Most financial reports contain a footnote to the effect of ``numbers might not add up due to rounding``.</P><P> </P><P>One remedy is:</P><P> </P><P>In P1 (first part): =ROUND(T1*D1%, 0)</P><P>In P2:P100 (other parts): =ROUND($T$1*SUM($D$1:D2)% - SUM($P$1:P1), 0)</P><P> </P><P>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``.</P><P> </P><P>-----</P><P> </P><P><LI-USER uid="1434209"></LI-USER> wrote: ``trying to teach myself Excel ``</P><P> </P><P>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%).</P><P> </P><P>Most people do not know that "%" is an operator that means "divide by 100".</P><P> </P><P>Nevertheless, I would recommend that you have actual percentages in D1 et al. For example, 1.3636363636<STRONG><FONT color="#DF0000">%</FONT></STRONG> instead of 1.3636363636. Simply format D1 as Percentage.</P><P> </P><P>Then you can write simply D1 instead of D1% everywhere.</P><P> </P><P>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.</P>Thu, 23 Jun 2022 20:12:03 GMThttps://techcommunity.microsoft.com/t5/excel/trying-to-calculate-distribution-percentages-dealing-with/m-p/3545364#M151616Joe User2022-06-23T20:12:03Z