How to: Proportionally Distribute Remainder

[jdpro]

Hi,

I have a budgeting app that is missing a feature, so I need to perform this calculation manually. I would like to have excel do it for me if possible.

Is there a way to distribute a remainder proportionally across the separate categories? In the sample below I need to divide 1.26 among the 4 categories proportionally. How do I do this -- without having to multiply column b by .0316 then adding them separately? Maybe I just have to do it that way. Thanks if you have a better way.

 

[eduzs]

The formula in C1 can be for example: =$b$8*($B1/$B$5) ???

 

[shg]

To avoid fractional cents,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Cat​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

Add​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Category 1[/td][td]

14.99​

[/td][td="bgcolor:#CCFFCC"]

0.4700​

[/td][td]

15.46​

[/td][td="bgcolor:#CCFFCC"]C2: =ROUND((B2) / ($B$6 - SUM(B$1:B1)) * ($B$9 - SUM(C$1:C1)), 2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Category 2[/td][td]

9.99​

[/td][td="bgcolor:#CCFFCC"]

0.3200​

[/td][td]

10.31​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Category 3[/td][td]

12.38​

[/td][td="bgcolor:#CCFFCC"]

0.3900​

[/td][td]

12.77​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Category 4[/td][td]

2.50​

[/td][td="bgcolor:#CCFFCC"]

0.0800​

[/td][td]

2.58​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td="bgcolor:#E5E5E5"]

39.86​

[/td][td][/td][td="bgcolor:#E5E5E5"]

41.12​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#F3F3F3"]

Tot Recpt​

[/td][td]

41.12​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#F3F3F3"]

Adj​

[/td][td="bgcolor:#E5E5E5"]

1.26​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[jdpro]

A year and a half later, I have been using this regularly. Had to come here to find this so I could recreate the sheet. Thanks again, shg.

Other calculations in this sheet: B6: =SUM(B2:B5); B9: =B8-B6; D6: =SUM(D2:D5);