From: K. Carter on 6 May 2010 09:42 Thanks Niek. Unfortunately, I cannot utilize more than 2 decimal places as the amounts represent dollars and cents that need to be split out to different accounts, to which I cannot charge a fraction of a cent. I was hoping to find a formula that would look at the all of the broken down figures by component and round or adjust just one of them to reach the desired total. "Niek Otten" wrote: > Then you'll have to round the intermediate results or the percentage. > If you format the cells to show more decimals, you'll see that they add up > perfectly to the right amount. But if you use, for example, 4.76199174% > to multiply with and you show only two decimals, they appear not to add up > correctly. > >  > Kind regards, > > Niek Otten > Microsoft MVP  Excel > > "K. Carter" <KCarter(a)discussions.microsoft.com> wrote in message > news:9D2892FDF634471F8A6F3E37B1D6C5DF(a)microsoft.com... > >I need a formula that will breakdown an original number with two components > > into multiple components which then add up to the original. The example > > below was generated with straight original x %, which ended up generating > > a > > $0.01 difference. I need the broken up figures to add up to the orginal > > to > > the penny. Thanks for your help! > > $5,475.00 + $273.75 = $5,748.75 > > 28.55% = $1,563.11 + $78.16 = $1,641.27 > > 24.42% = $1,337.00 + $66.85 = $1,403.85 > > 19.25% = $1,053.94 + $52.70 = $1,106.64 > > 13.95% = $763.76 + $38.19 = $801.95 > > 13.83% = $757.19 + $37.86 = $795.05 > > > > 100.00%= $5,475.00 + $273.76 = $5,748.76 ($0.01 too much) > > >
From: Niek Otten on 6 May 2010 14:35 Calculate one of the figures, round to 2 decimals and subtract from the original to get the second figure If I don't seem to get what you rquire, explain what you're trying to achieve in business terms, not Excel terms  Kind regards, Niek Otten Microsoft MVP  Excel "K. Carter" <KCarter(a)discussions.microsoft.com> wrote in message news:F202EE5FB91742A180520F25D8972C71(a)microsoft.com... > Thanks Niek. Unfortunately, I cannot utilize more than 2 decimal places > as > the amounts represent dollars and cents that need to be split out to > different accounts, to which I cannot charge a fraction of a cent. I was > hoping to find a formula that would look at the all of the broken down > figures by component and round or adjust just one of them to reach the > desired total. > > "Niek Otten" wrote: > >> Then you'll have to round the intermediate results or the percentage. >> If you format the cells to show more decimals, you'll see that they add >> up >> perfectly to the right amount. But if you use, for example, 4.76199174% >> to multiply with and you show only two decimals, they appear not to add >> up >> correctly. >> >>  >> Kind regards, >> >> Niek Otten >> Microsoft MVP  Excel >> >> "K. Carter" <KCarter(a)discussions.microsoft.com> wrote in message >> news:9D2892FDF634471F8A6F3E37B1D6C5DF(a)microsoft.com... >> >I need a formula that will breakdown an original number with two >> >components >> > into multiple components which then add up to the original. The >> > example >> > below was generated with straight original x %, which ended up >> > generating >> > a >> > $0.01 difference. I need the broken up figures to add up to the >> > orginal >> > to >> > the penny. Thanks for your help! >> > $5,475.00 + $273.75 = $5,748.75 >> > 28.55% = $1,563.11 + $78.16 = $1,641.27 >> > 24.42% = $1,337.00 + $66.85 = $1,403.85 >> > 19.25% = $1,053.94 + $52.70 = $1,106.64 >> > 13.95% = $763.76 + $38.19 = $801.95 >> > 13.83% = $757.19 + $37.86 = $795.05 >> > >> > 100.00%= $5,475.00 + $273.76 = $5,748.76 ($0.01 too much) >> > >>

Pages: 1 Prev: How can I restore a column that was deleted by mistake Next: If condition with text 