Calculating three most efficient lengths

[Nanaia]

What formula, or combination of formulae, would be uses to look at all the numbers in a column and calculate the three sheet lengths they would fit best on. (does that make sense?)
Goal: To determine which three sheet lengths would be most efficient for the part lengths to nest into for a given job (metal coil industry). Minimum sheet length 86”, maximum 150”. Sheets are cuts in increments of 6”.
I understand which formulas to use to find out the largest number in the column, the shortest number in the column, and the number that occurs the most often. I’m not sure if I can accomplish my goal on this, but I figured this is Excel, there’s a way to do this.

[Richard Buttrey]

You may find this Microsoft optimal solver article give you a way forward.

Incidentally, would you update your location to something meaningful. It often helps when we need to consider stuff like dates and regioanl settings.

[MarvinP]

Hi Nanaia,

A few example problems with answers would be needed to start thinking about this problem. If you could even put them on a workbook and attach it to the question, that would be a big help. You should also tell us what "efficient" means. Does that mean the least number of cuts or the least material left over.

[Nanaia]

"Efficient" = having the least amount of waste. For example, if the part is 90" long it would fit on a 96" sheet more efficiently than on a 150" sheet. Also, a 44" plus a 45" and a 35" would bit better on a 126" sheet than 144" sheet or even a 120" sheet because there is less waste.

I don't have anything put together yet to post here so it could be edited. The only other thing I can think to add is that the parts range in length from 6" to 146".

[alansidman]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

https://www.mrexcel.com/forum/excel-...t-lengths.html

[MrShorty]

Since alansidman provided the link, I will assume it is okay to comment:

It sounds like a pretty standard cutting stock problem.

http://blog.excelmasterseries.com/20...aste-with.html
https://www.solver.com/cutting-stock
or put "Excel cutting stock problem" into your favorite internet search engine. I know there are also examples of "cutting stock" and "knapsack" problems (cutting stock is just a variation of the knapsack problem) on this forum. such as: https://www.excelforum.com/excel-gen...warehouse.html

See if any of those resources help.

[Nanaia]

I'd never heard the term "knapsack". Thank you for your suggestions.

[AliGW]

The Happy Wanderer

I love to go a-wandering
Along the mountain track
And as I go, I love to sing
My knapsack on my back
Val-deri, val-dera
Val-deri, val-dera
Ha, ha, ha, ha, ha, ha
Ha
Val-dera
My knapsack on my back
I love to wander by the stream
That dances in the sun
So joyously it calls to me
Come join my happy song
Val-eri, val-dera
Val-deri, val-dera
Ha, ha, ha, ha, ha, ha
Ha
Val-era
Come join my happy song
I wave my hat to all I meet
And They wave back to me
And blackbirds call so loud and sweet
From ev'ry green wood tree
Val-eri, val-dera
Val-deri, val-dera…