Hi everyone,
I'm trying to make an excel sheet that allows me to calculate the number of pallets from a list of box dims.
So for example I enter the outer box dims in CM from the cartons on an order we are going to palletize I would then input the max pallet height as for example “1.8“ and excel would then calculate the number of pallets the order would approximately use but still display it as "X Pallets".
I do have a very basic understanding of excel so any help would be appreciated
See reply here: same problem (reply from Mr Shorty) ... no easy solution.
https://www.excelforum.com/excel-gen...ghlight=Pallet
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Administrative Note:
Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sorry maybe I over complicated it.
In one cell I have a formula calculating the CBM of an order from the products outer carton.
In table of data I have; the text I would like to be displayed (in this instance "1 Pallet" thru to "40 Pallets", in the next column over I have the corresponding CBM for the number of pallets at a set height.
What I'd like to do is have excel take the value from the cell with the order CBM calculation in it, approximately match it to the list and then display the corresponding text.
Hope that helps,
Thanks
that sounds like a simple lookup function (VLOOKUP() is a common starting place for Excel's lookup functions: https://www.ablebits.com/office-addi...ximate-vlookup Since, as you indicate, I expect you will want an "approximate match" algorithm be sure to study those examples so you understand how the approximate match algorithm works.What I'd like to do is have excel take the value from the cell with the order CBM calculation in it, approximately match it to the list and then display the corresponding text.
Originally Posted by shg
Thank you for your reply MrShorty, if I'm completely honest I still couldn't make it work so assumed that either I was barking up the wrong tree or its just not possible to do what I intended due to the mentioned knapsack problem.
I did however find another way of doing this but slightly differently so I do have another question, if that's okay.
This new file takes the dims of the outer cartons of the ordered, items works out the cubage of the items individually, times' the total cubage by the ordered quantity, works out the total cubage and then devides that by the cubage of a std plt at a set height and then rounds the number to produce a rough number of pallets or boxes. My question is: would this work? Does it make sense?
Thank you
Does it make sense -- yes, as long as the goal is to get and approximate number of pallets, total cubage/cubage per pallet, should give an approximate (or minimum, I'm not sure) number of pallets required. This simple relationship assumes no wasted cubage -- that you can effectively pack the boxes onto each pallet so that the pallet is completely full -- which is why I think it will return a minimum number of pallets. You would probably have to try it with your actual box dimensions and see how closely it approximates the actual number of pallets. In the end, that might still mean actually solving the packing problem (or finding a routine or non-Excel app that can solve it).
Total cubage/cubage per pallet*(1+10%), add 10% as a buffer, that's what we normally do.
@ Blxxdline_
Pleae see post #3 and update your forum profile NOW. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks