I need to determine how much liner feet of different products with varying widths I need, based on dimensions provided. Excel 2003. My spreadsheet has:

□ A number assigned to each part in the job (windows in this case, ie: window 1, window 2)

□ A Validation drop down list of the materials pulled from columns for each material with product codes and widths

□ Dimensions of each window, width and height

Worksheet #1, the Window numbers are assigned and dimensions are entered.

Worksheet #2, the Window numbers are automatically entered from WS#1 and the user chooses which material from the Validation drop downAt this point in a group of cels in Worksheet #3, based on the Window number and the chosen material from WS#2 I would like to:

□ look at the dimensions from WS#1, take the height and divide by the selected materials width, rounding up to multiples of the material width (eg: material width is 60 inches, window height is 95 inches, it will take 2 section widths of the material to cover that area)

□ multiply the rounded number of sections by the width (this gives me the linear feet required for that window

□ but... if the window width is less than the selected material width I want to invert the material and do a straight linear footage calculation based on window height. I need the formula to recognize this based on the material selected in WS#2There will be multiple materials, so this will generate a list of materials selected and how much linear footage is required for each. At this point I can take the linear footage and calculate costs.

Kind of complex but I hope someone can offer some ideas. Thanks.

What if the waste area can do another window? Most material estimation would take the the total area needed and apply a loss factor and calculate the cost. Unless the material is very expensive such detail is not needed.

Excess material is considered lost. My example was probably a poor one in that the heights used never have excess material that can be used elsewhere. The material is in linear footage by fixed width and is costed as such. It's a mater of how much liner material is required per window.

Ask Your Question

Weekly Poll