Best Approach for Determining Correct Thickness Profile

[alyoshablue]

Hello, Excel Peoples:

I am looking for suggestions to approach solving this problem. I will explain it first, in an attempt to express the logic.

I work for a company that sprays a product on a steel beams in a building. The amount of product that goes onto that member is established by building codes and can be calculated by a formula. Essentially, smaller beams require greater material thicknesses than larger beams.

I have constructed the following table to do the math for me, and essentially, the table goes from left to right. Specifically, the logic flow is: (D) Insert beam designation, (E) Input length of those beam(s), (F) Calculate the code required thickness, (G) Round up to the nearest 1/16th of an inch, (H) Vlookup a coverage factor based on (G), (I) Calculate the total Board Feet, (J) Divide (I) by the amount of Board Feet in my bag of material, and (K) Get the final number of bags from all the math. The table is below:

Capture1.JPG

Ultimately, I will use this table to tell my crew what to spray on the beams. I cannot have them spray each individual beam it's calculated thickness, as it is too time consuming. So, now, I tell the crews to spray all the beams at 1.375 inches, as it is easier to tell the crew what the thickness is and then they just spray away. What I would like to do is give them 2 or 3 thicknesses, so I can save money by using less material. So, essentially, my question is:

How do I ideally divide up column (G) thicknesses so that I reach a minimum amount of total bags? The only variable I have is the spray thickness and it can never go lower than it's adjacent cell to the left, column (F). Ideally, I would expect to see something like this, where the spray thicknesses are broken down into an ideal thickness profile:

Capture2.JPG

At the moment, I feel solver is the way to go and I have a feeling that I will need some commercial help. However, I would like to take a stab at it myself, before giving up. Any thoughts from the group?

All assistance is truly appreciated. I will answer any question you may have.

[jason.b75]

Maybe round up to a lower factor, i.e 1/8 inch instead of 1/16?

Or another lookup table with approximate matching.

[alyoshablue]

I appreciate the feedback, Jason.

Maybe I should further expand on what I am looking for.

If I "tell" the table that I want three thicknesses, I want excel to determine where the splits should be that would give me the least amount of bags for three splits. One would think, there would be iterative calculations to determine just where they are.

Excel can then tell me where they are and what they are. Or put another way, what are the three thicknesses and where are they applied to receive the least amount of bags.

[jason.b75]

This is the best formula solution that I can come up with, results are close to your sample but not identical. I haven't compared bag totals to see if it gives the minimum possible.

Assuming that your data starts in row 2, this goes into G2.

=MIN(CEILING(F2,FLOOR((CEILING(MAX($F$2:$F$19),1/16)-CEILING(MIN($F$2:$F$19),1/16))/($L$2-1),1/16)),CEILING(MAX($F$2:$F$19),1/16))

Where L2 contains the number of thicknesses to split into.

I don't think that solver will be capable of giving you a suitable result. Maybe someone else will have a better idea of how to make that work as needed.

It would be better if you could attach a sample workbook for people to test with so that they don't have to re-type your data.

[alyoshablue]

Jason:

Thanks for applying thought to that. I tried your suggestions and they somewhat worked. My attempt to upload a spreadsheet have gone unsuccessful, but I think that is because you have to start the post with it.

I will regroup and perhaps try a few other things.

Thanks for your help, though.