teacherr1
New Member
- Joined
- Nov 15, 2021
- Messages
- 8
- Office Version
- 365
- 2021
- Platform
- Windows
- Mobile
- Web
Hello everybody,
Could please someone check if my workarounds are possible to achieve?
I tried to approach it different ways but so far i only managed to prepare data placement with few easy formulas such as =mod =quotient and other cells references.
Basicly its about Box on pallets calculations BUT, what is unique in that case is that every different type of mixedBOXES need to be put on EPAL , as its mandatory client requirement.
So for mixing just 2 types of different boxes we have 30cm less from max255cm, thanks to EPAL sizes...
boxes can only be stacked in Height, inside boxes there are buckets with water so any other positioning is not possible, unless towardsUp.
So we have stacking structure of:
-> maximum 255cm <-
EPAL
-> layer with boxes -<
EPAL
-> layer with boxes <-
EPAL
so its not only reducing total height but also force to mix only same layers height boxes to make a flat surface for epal on top,
- with that requirements its in fact hard to achieve mixes larger than 3 box layers, but only two fits in most of cases...
The main goal is to point automaticly which mixes are most optimal to reduce total order pallets.
Below i wanted to try cubicCM/load volume but cant make it that to work either...
Using % to check current height of the remaining boxes for mixing, if its already above 75% we can count this as fullpallet beacuse just adding another 15cm to 75% is making any mixes impossible.
I just need to find those combinations of mixes that are possible to make with given numbers. Need for that workaround is that all those boxes are made by production daily, so they are not produced in same order each day, so we need to know already what are possibilities for all mixes so we wont wait for all boxes till end of the day.
We know exact order amounts days before, and right now we need to add heights of boxes manually and its for longer run really exhausting with all that data.
So please, could someone take a look and check if my current solutions are worth any efficiency?
With regards,
Could please someone check if my workarounds are possible to achieve?
I tried to approach it different ways but so far i only managed to prepare data placement with few easy formulas such as =mod =quotient and other cells references.
Basicly its about Box on pallets calculations BUT, what is unique in that case is that every different type of mixedBOXES need to be put on EPAL , as its mandatory client requirement.
So for mixing just 2 types of different boxes we have 30cm less from max255cm, thanks to EPAL sizes...
boxes can only be stacked in Height, inside boxes there are buckets with water so any other positioning is not possible, unless towardsUp.
So we have stacking structure of:
-> maximum 255cm <-
EPAL
-> layer with boxes -<
EPAL
-> layer with boxes <-
EPAL
so its not only reducing total height but also force to mix only same layers height boxes to make a flat surface for epal on top,
- with that requirements its in fact hard to achieve mixes larger than 3 box layers, but only two fits in most of cases...
The main goal is to point automaticly which mixes are most optimal to reduce total order pallets.
Below i wanted to try cubicCM/load volume but cant make it that to work either...
Using % to check current height of the remaining boxes for mixing, if its already above 75% we can count this as fullpallet beacuse just adding another 15cm to 75% is making any mixes impossible.
I just need to find those combinations of mixes that are possible to make with given numbers. Need for that workaround is that all those boxes are made by production daily, so they are not produced in same order each day, so we need to know already what are possibilities for all mixes so we wont wait for all boxes till end of the day.
We know exact order amounts days before, and right now we need to add heights of boxes manually and its for longer run really exhausting with all that data.
So please, could someone take a look and check if my current solutions are worth any efficiency?
With regards,
| test.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | CM | 255 cm = maxTotalHeight | needtoAdd'Height +15CM for EPAL | copy optimal combination that match for MIXing, and use same cell background color, different for every mixes pair-triple combination | |||||||||||||
| 2 | Lenght | Width | Height | BOXperPallet | boxLayer | totalLayers | totalHeight | TotalBoxHeight+EPAL | RemainingCM | orderBOX | fullPLTS | mixBOX | MixSize% | ||||
| 3 | 40,00 | 26,50 | 70,00 | 27 | 9 | 3 | 210,00 | 225,00 | 30,00 | 9 | 0 | 9 | 33% | ||||
| 4 | 29,00 | 26,00 | 34,00 | 84 | 12 | 7 | 238,00 | 253,00 | 2,00 | 156 | 1 | 72 | 86% | ||||
| 5 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 222 | 4 | 42 | 93% | ||||
| 6 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 231 | 5 | 6 | 13% | ||||
| 7 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 412 | 9 | 7 | 16% | ||||
| 8 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 333 | 7 | 18 | 40% | ||||
| 9 | 26,00 | 29,00 | 60,00 | 48 | 12 | 4 | 240,00 | 255,00 | 0,00 | 12 | 0 | 12 | 25% | ||||
| 10 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 270 | 6 | 0 | 0% | ||||
| 11 | 40,00 | 26,50 | 47,00 | 45 | 9 | 5 | 235,00 | 250,00 | 5,00 | 721 | 16 | 1 | 2% | ||||
| 12 | 26,00 | 29,00 | 60,00 | 48 | 12 | 4 | 240,00 | 255,00 | 0,00 | 54 | 1 | 6 | 13% | ||||
| 13 | 50,00 | 30,00 | 59,00 | 24 | 6 | 4 | 236,00 | 251,00 | 4,00 | 24 | 1 | 0 | 0% | ||||
| 14 | 40,00 | 30,00 | 60,00 | 32 | 8 | 4 | 240,00 | 255,00 | 0,00 | 40 | 1 | 8 | 25% | ||||
| 15 | 40,00 | 26,50 | 60,00 | 36 | 9 | 4 | 240,00 | 255,00 | 0,00 | 0 | 0 | 0% | |||||
| 16 | 60,00 | 40,00 | 34,00 | 28 | 4 | 7 | 238,00 | 253,00 | 2,00 | 60 | 2 | 4 | 14% | ||||
| 17 | 60,00 | 40,00 | 34,00 | 28 | 4 | 7 | 238,00 | 253,00 | 2,00 | 0 | 0 | 0% | |||||
| 18 | 60,00 | 40,00 | 34,00 | 28 | 4 | 7 | 238,00 | 253,00 | 2,00 | 24 | 0 | 24 | 86% | ||||
| 19 | 60,00 | 40,00 | 34,00 | 28 | 4 | 7 | 238,00 | 253,00 | 2,00 | 0 | 0 | 0% | |||||
| 20 | 40,00 | 26,50 | 60,00 | 36 | 9 | 4 | 240,00 | 255,00 | 0,00 | 32 | 0 | 32 | 89% | ||||
| 21 | 50,00 | 30,00 | 47,00 | 30 | 6 | 5 | 235,00 | 250,00 | 5,00 | 30 | 1 | 0 | 0% | ||||
| 22 | |||||||||||||||||
| 23 | |||||||||||||||||
| 24 | |||||||||||||||||
| 25 | EURO PALLET | ||||||||||||||||
| 26 | CM | ||||||||||||||||
| 27 | L | 120 | 120 | 120 | |||||||||||||
| 28 | W | 80 | 80 | 80 | |||||||||||||
| 29 | H | 15 | 240 | 255 | |||||||||||||
| 30 | |||||||||||||||||
| 31 | cubCM | 144000 | 2304000 | 2448000 | |||||||||||||
| 32 | |||||||||||||||||
| 33 | emptyPallet | maxBoxes | maxBoxes+Plt | ||||||||||||||
Sheet1 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3:F21 | F3 | =D3/E3 |
| G3:G21 | G3 | =F3*C3 |
| H3:H21 | H3 | =G3+15 |
| I3:I21 | I3 | =MOD(255,H3) |
| L3:L21 | L3 | =QUOTIENT(K3,D3) |
| M3:M21 | M3 | =MOD(K3,D3) |
| N3:N21 | N3 | =M3/D3 |
| B31:D31 | B31 | =B27*B28*B29 |
