Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for dividing work
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for dividing work
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 "Dok" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for dividing work
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 "Peter" wrote: Did you add the truck number into the load on each truck? I put the data into a spreadsheet and reorganized it just to see if I understood 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 averages 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 "Dok" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for counting and dividing | Excel Worksheet Functions | |||
SUMPRODUCT: dividing the formula of one cell by another. | Excel Worksheet Functions | |||
What is the formula for dividing-I have the total | Excel Worksheet Functions | |||
Formula needed for dividing hours by numbers | Excel Discussion (Misc queries) | |||
What is the formula for dividing a number by 4 in excel | Excel Worksheet Functions |