Prev: TABLE ROW SORT ERROR?
Next: Conditional Formatting according to the date the data is entered
From: K. Carter on 5 May 2010 16:06
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 5 May 2010 17:17
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: TABLE ROW SORT ERROR?
Next: Conditional Formatting according to the date the data is entered