# Ratio Percent distribution

#### Arinoum

##### Board Regular
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### JoeMo

##### MrExcel MVP
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

#### Arinoum

##### Board Regular
Yes! Thank you Angel of the Internet

#### Arinoum

##### Board Regular
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>

 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

##### MrExcel MVP
Yes! Thank you Angel of the Internet
You are welcome - thanks for the reply.

Replies
12
Views
485
Replies
1
Views
9K
Replies
2
Views
1K
Replies
42
Views
3K
Replies
2
Views
996

1,191,686
Messages
5,988,004
Members
440,125
Latest member
vincentchu2369

### 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?

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