Ratio Percent distribution

Arinoum

Board Regular
Joined
Sep 28, 2016
Messages
64
Hi All,
I am trying to distribute the same ration percent even after I change the GM%. Here is what I have:



Salary Cost 60%
Holiday cost 5%
Payroll taxes 10%

All the above = 75% which mean my GM% is 25%
What I am trying to do is change the cell that contains the GM% to let's say 30% but I would like the cells that contain the ratio % to distribute evenly per the historical ratios to equal 70%

So if I change my GM% cell to 30% Cells B1,B2,B3 should automatically calculate 60% of the 70%, then 5% of the 70% and then 10% o the 70%. What should be my formula?

Sorry if I did not explain this right I am having trouble articulating it.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Does this work for you?
Excel Workbook
ABCDEFG
1Historical Values**Target GM*Required Values*
2SalCostHist60%*30%*Salary Cost56.00%
3HolidayCostHist5%***Holiday Cost4.67%
4PayRollTaxHist10%***Payroll Taxes9.33%
5GMHist25%***Tot Cost70.00%
Sheet4
 
Upvote 0
Does this work for you?
Sheet4

*ABCDEFG
1Historical Values**Target GM*Required Values*
2SalCostHist60%*30%*Salary Cost56.00%
3HolidayCostHist5%***Holiday Cost4.67%
4PayRollTaxHist10%***Payroll Taxes9.33%
5GMHist25%***Tot Cost70.00%

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:101px;"><col style="width:64px;"><col style="width:36px;"><col style="width:70px;"><col style="width:33px;"><col style="width:93px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G2=B2*(1-$D$2)/SUM($B$2:$B$4)
G3=B3*(1-$D$2)/SUM($B$2:$B$4)
G4=B4*(1-$D$2)/SUM($B$2:$B$4)
B5=1-SUM(B2:B4)
G5=SUM(G2:G4)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Yes! Thank you Angel of the Internet :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top