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" wrote in message > news:9D2892FD-F634-471F-8A6F-3E37B1D6C5DF(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" wrote in message news:F202EE5F-B917-42A1-8052-0F25D8972C71(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" wrote in message >> news:9D2892FD-F634-471F-8A6F-3E37B1D6C5DF(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 mistakeNext: If condition with text