Ratio Percent distribution

[Arinoum]

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.

 

[JoeMo]

Does this work for you?

Excel Workbook
	A	B	C	D	E	F	G
1	Historical Values	*	*	Target GM	*	Required Values	*
2	SalCostHist	60%	*	30%	*	Salary Cost	56.00%
3	HolidayCostHist	5%	*	*	*	Holiday Cost	4.67%
4	PayRollTaxHist	10%	*	*	*	Payroll Taxes	9.33%
5	GMHist	25%	*	*	*	Tot Cost	70.00%
Sheet4

 

[Arinoum]

Yes! Thank you Angel of the Internet

 

[Arinoum]

Does this work for you?
Sheet4

*	A	B	C	D	E	F	G
1	Historical Values	*	*	Target GM	*	Required Values	*
2	SalCostHist	60%	*	30%	*	Salary Cost	56.00%
3	HolidayCostHist	5%	*	*	*	Holiday Cost	4.67%
4	PayRollTaxHist	10%	*	*	*	Payroll Taxes	9.33%
5	GMHist	25%	*	*	*	Tot Cost	70.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

Cell Formula 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

 

[JoeMo]

Yes! Thank you Angel of the Internet

You are welcome - thanks for the reply.