How to: Proportionally Distribute Remainder

jdpro

New Member
Joined
May 1, 2016
Messages
29
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.

 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
To avoid fractional cents,

A​
B​
C​
D​
E​
1​
Cat
Amt
Add
Total
2​
Category 1
14.99​
0.4700​
15.46​
C2: =ROUND((B2) / ($B$6 - SUM(B$1:B1)) * ($B$9 - SUM(C$1:C1)), 2)
3​
Category 2
9.99​
0.3200​
10.31​
4​
Category 3
12.38​
0.3900​
12.77​
5​
Category 4
2.50​
0.0800​
2.58​
6​
39.86​
41.12​
7​
8​
Tot Recpt
41.12​
9​
Adj
1.26​
 

jdpro

New Member
Joined
May 1, 2016
Messages
29
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);
 

Watch MrExcel Video

Forum statistics

Threads
1,090,497
Messages
5,414,892
Members
403,552
Latest member
Daniel Kuenstler

This Week's Hot Topics

Top