Formula for dividing work

[Dok]

He is the situation. I have 15 trucks a day to load each a different volume.
I have 3 or 4 loaders per day. I need a formula that would allow me to have
the trucks assigned to each loader so that they each end up with about the
same volume for the day.



Truck Volume Loader
101 237 160 700 1198
102 236 151 654 1143
103 221 75 980 1379 Loaders
104 186 131 867 1288 Bob
105 301 186 955 1547 John
106 294 67 675 1142 Rick
107 156 167 755 1185
108 221 320 846 1495
109 234 454 946 1743
110 265 234 984 1593
111 274 222 344 951
112 301 443 944 1800
113 268 341 867 1589
114 186 564 754 1618
115 257 355 688 1415
116 255 186 901 1458
Avg. Vol 1328

 

[Peter]

Did you add the truck number into the load on each truck? I put the data
into a spdsheet and reorganized it just to see if I understoond the
problem--16 trucks, 3 different sized loads listed per truck, actual average
on the lower right...is this the right way to look at the data? Not sure if
there is a set way to do this, or if sorting the loads and then running some
kinds of aveages would work. Does each truck have a different max load?

Regards,
Peter
# Ld1 Ld2 Ld3 xxx actual load minus truck #???
101 237 160 700 1198 1097
102 236 151 654 1143 1041
103 221 75 980 1379 1276
104 186 131 867 1288 1184
105 301 186 955 1547 1442
106 294 67 675 1142 1036
107 156 167 755 1185 1078
108 221 320 846 1495 1387
109 234 454 946 1743 1634
110 265 234 984 1593 1483
111 274 222 344 951 840
112 301 443 944 1800 1688
113 268 341 867 1589 1476
114 186 564 754 1618 1504
115 257 355 688 1415 1300
116 255 186 901 1458 1342
Avg. Vol 1328 Average 1300.5

 

[Peter]

Dok---it's out of my league but I'll be interested if anyone can help you
more than this simple suggestion below... in lieu of some add in called Excel
Solver that seems like the real deal for linear optimization...

I took the 48 loads and sorted them by size. Then I divided them into
groups 1-16, 17-32 and 33-48. I put these in 3 columns side by side and
flipped the heaviest third (they're on the right) by sorting them in reverse
order. Then I simply added each triplet. The resulting loads for each truck
are not optimal, but they are in line with the average load you wanted.

In effect, consider a graph of the 48 loads. It would be a line from the
lower left to the upper right of a graph. My sorting process simply divided
this into 3 lines, and picked off one selection from each far end then worked
inwards, and to each pair added one from the middle, to make a set of
triplets of relatively similar weight.

Maybe a fancier version could sort the middle section hi/lo, hi/lo by
renumbering them from say 8 to 7 ... to -7,-8 and then sorting by the
absolute value (abs function) so that the 8's are paired, the 7's and so
on....

The loaders could then be rows 1 to 5 for one, 6-11 and 12-16 for the other 2.

Lo Mid Hi Total triplet on each row
1 67 17 236 48 984 1287
2 75 18 237 47 980 1292
3 131 19 255 46 955 1341
4 151 20 257 45 946 1354
5 156 21 265 44 944 1365
6 160 22 268 43 901 1329
7 167 23 274 42 867 1308
8 186 24 294 41 867 1347
9 186 25 301 40 846 1333
10 186 26 301 39 755 1242
11 186 27 320 38 754 1260
12 221 28 341 37 700 1262
13 221 29 344 36 688 1253
14 222 30 355 35 675 1252
15 234 31 443 34 654 1331
16 234 32 454 33 564 1252

Hope this helps!

Regards,
Peter