Allocating multiple selections to multiple people

redneckopen

Board Regular
Joined
Nov 2, 2004
Messages
224
Lets say in A2 thru A5 I have 4 different codes for one product all with different amounts represented in B2 thru B5. I then have 7 different people I need to allocate those amounts to in columns C1 thru I1 evenly against their order in C2 thru I2. What would be the algorithm that could be used?

eg.
GROUP 1 GROUP 2 GROUP 3 GROUP 4 GROUP 5 GROUP 6 GROUP 7
7,500 18,122 3,545 21,776 12,124 5,488 7,210
SCB 26,542
SCW 15,885
SCA 7,122
SCV 65,124
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Something like:


Excel 2010
ABCDEFGHI
1GROUP1GROUP2GROUP3GROUP4GROUP5GROUP6GROUP7
27,50018,1223,54521,77612,1245,4887,21075,765
30.098990.2391870.0467890.2874150.1600210.0724350.0951631
4GROUP1GROUP2GROUP3GROUP4GROUP5GROUP6GROUP7
5SCB26,542.002,627.406,348.501,241.887,628.574,247.281,922.562,525.81
6SCW15,885.001,572.463,799.48743.254,565.592,541.941,150.621,511.66
7SCA7,122.00705.011,703.49333.232,046.971,139.67515.88677.75
8SCV65,124.006,446.6415,576.813,047.1118,717.6210,421.224,717.226,197.37
Sheet8
Cell Formulas
RangeFormula
H2=SUM(A2:G2)
A3=A2/$H$2
C5=A$3*$B5
 
Upvote 0
That isn't quite correct, Group1 order is 7,500 but it is allocating 11,351.51 to group1 from the 4 codes.
 
Upvote 0
Do you want an equal percentage to each? Or does the 26,542, 15,885, 7122, and 65,124 determine which goes where?
 
Upvote 0
Yes, it needs to be allocated against the totals available. The reason (if it matters) is to minimize and spread out risk between customers.
 
Upvote 0
This?


Excel 2010
ABCDEFGHIJ
1GROUP1GROUP2GROUP3GROUP4GROUP5GROUP6GROUP7
27,50018,1223,54521,77612,1245,4887,210
3
4GROUP1GROUP2GROUP3GROUP4GROUP5GROUP6GROUP7
5SCB26,542.000.231,735.944,194.48820.525,040.232,806.201,270.241,668.81
6SCW15,885.000.141,038.932,510.34491.073,016.511,679.47760.22998.76
7SCA7,122.000.06465.801,125.50220.171,352.44752.99340.84447.79
8SCV65,124.000.574,259.3310,291.672,013.2412,366.826,885.353,116.694,094.63
9114,673.00
Sheet8
Cell Formulas
RangeFormula
C5=B5/$B$9
D5=$C5*A$2
B9=SUM(B5:B8)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,993
Messages
6,175,843
Members
452,675
Latest member
duongtruc1610

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