Prev: How can I restore a column that was deleted by mistake
Next: If condition with text
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: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" <KCarter(a)discussions.microsoft.com> 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" <KCarter(a)discussions.microsoft.com> 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 mistake
Next: If condition with text