Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to breakdown original amount with two components to the pe
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to breakdown original amount with two components to the pe
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 ... 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to breakdown original amount with two components to th
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 ... 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to breakdown original amount with two components to th
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 ... 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 ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage Breakdown Formula Help? | Excel Discussion (Misc queries) | |||
Quick Go To for Formula Components Other than First | Excel Worksheet Functions | |||
Quick Go To for Formula Components Other than First | Excel Worksheet Functions | |||
Formula for amount owing subtract amount paid | Excel Worksheet Functions |