How do I calc based on several factors

[rfcomm2k]

I would search the forum for an answer to this but I don't even know what to search for to begin with.

Here is issue. I am building a workbook to estimate remodeling costs for prospective projects. One sheet (pricing) has all material pricing (2x4x8, 2x4x10, 2x4x12, etc) for all lumber and material (drywall, studs, pressure treated deck material, etc).
Another sheet (tasks) lets me input info about the project (example, for a deck it asks height above ground, length, width, stair height, material type etc)
A third sheet (project) calcs the material needed for the given task.

This all works OK for a deck under 16' x 16'. But beyond that I forgot to factor in the additional beam and joist material. Problem is, I just realized that the material used could depend on the standard size lumber and the size of deck. For examples:

A deck 8' long uses an 8' beam
A 16' deck could use a 16' beam or two 8' beams
A 24' deck could use two 12' beams or three 8' beams
A 29' deck could use two 12' beams and one 8' beam with 3' leftover, or three 10' beams with 1' leftover.

Which beam configuration to choose depends on the least amount of waste but also depends on the current price for a given combination. Today a 10' 2x12 is cheapest per foot, but tomorrow the 12' or the 8' size could be cheapest.

Another mitigating factor is the 8' post spacing for the supports. That dictates using lengths that are multiples of 8, but if there is an overhang at each end that makes a difference. This overhang is available as input but is included in the overall length/width numbers (if I specify a deck is 10' x 10' with a 1' overhang the total sq footage of deck is still only 100).

So how do I figure out the cheapest combination to meet the beam length needed?

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[rfcomm2k]

I hope you are satisfied with the change in the title. If not, I suggest you change it yourself cuz I don't know what you are looking for.
FYI it DID accurately describe my original problem, that being I did not know what to search the forum for. If this problem can be solved using an array and I thought so, I would search the forum for "array". If you thought this might be solved using a lookup table, maybe you should have suggested I search for "lookup table" instead of being a forum cop and scolding me for not doing what YOU think I should.

In other words, if you have something constructive to suggest please do so, but just because the title is vague to you does not mean it is vague to me.

[zbor]

It's better now.
Also it did NOT describe your original problem because if your question is: I would search the forum but.....what to search for? then I would answer Search terms you need for your solution and I doubt that would give you answer you looking for. So what you would search for is probably best guide to title name.

Let's get focus to the problem.

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.


You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file.


Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.