Need help with BUILDING an Excel Formula with about 5 or 6 Parameters:

[whitarnold]

My name is ________ and I work at _______ Steel Company.... We build "Commercial Steel". We receive Joist and Deck Quotes from vendors and we just plug their numbers into our "in-house" spreadsheet that we use to estimate steel and we write out bid letters from that software. My problem is that 90% of the time the Deck is attached to the Joist via "Puddle Welds" which is fine and does not require us to change our LABOR for erecting a steel building with the numbers provided to us for Joist and Deck. HOWEVER, in that 10% of Drawing that we get in it will have a General Note about using Powder Actuated Fasteners instead of "Puddle Welds". We need to come up with a FORMULA that will allow someone in the office to point and click on some radials and receive a NEW PRICE to input for Powder Actuated Fasteners.
I will list the Parameter that I can think of with a short definition:
1) Zone 1 or 2 or 3 or Etc..... = A zone is a portion of the roof that has a certain ATTACHMENT PATTERN --- There are only four attachment patterns:
2)36/4......36/5......36/6......36/7 = What this means is that the deck (always 3' wide or 36") is attached with 4,5,6or7 P.A.F.Last every 36"......
3)We have only two types of Fasteners: HILTI X-HSN 24 and HILTI X-ENP 19......Each type has a price per fastener......
4)Last, we have the Joist spacing that the deck is attaching to..... It is always like 4'-0" or 5'-6" or 6'-0" Etc......

I need a formula that would allow us to enter the deck attachment type and the joist spacing....then the Square Foot in a zone........ Then I GET LOST and DO NOT KNOW HOW TO MAKE A algorithm..... LOL

PM me and maybe we can work something out over the phone????

[mcmahobt]

Do you have a spreadsheet in the works? Starting from scratch? Do you just need a setup where you can input the deck attachment type and the joist spacing then the square footage and it outputs...what exactly? What is your expected end result?

[whitarnold]

I do not have a spreadsheet for the deck attachment. I need one from scratch.... BUT I do have our in-house software that is used.... it was all built in Excel???


I would like to see the end results being: We could click a radial by the attachment type, then click a radial by the attachment pattern, then click a radial by the joist spacing, then ENTER a Square Foot.... Have a formula work the math in the background and give us out a NUMBER OF FASTENERS AND A PRICE FOR TOTAL FASTENERS.....That number would be the price for that particular ZONE....Then we would move on to the next zone???

[mcmahobt]

So based on your needs, this seems to be more of a math problem than an Excel problem. The Excel should be simple enough to figure out on the programming side, it's the math that is the holdup.

Before I try and make an attempt at putting together a formula, what do you mean by "click a radial"? Is that simply "select from dropdown" or the like?

[whitarnold]

Yes... Select from a down drop Or just have the options there and pick the one you want.... I think that the only two factors that would require data entry is a cell for Square Feet and a cell for Joist Spacing.....

[mcmahobt]

Would you not also need one for fasteners? Also, what is the relevance of the numbers being divided into 36 from your number 2 above? Does that correspond to your joint spacing?

[whitarnold]

You would need to select the FASTENER TYPE (That would determine the $Price Per Fastener) then you would need to select the FASTENER PATTERN (That would determine how many times it is going to happen) then you would need to select the JOIST SPACING (Or enter it in Ft. and In.) Then you would need to enter the SQUARE FEET (The formula should determine how many times it happens per sqft and then add them up and spit out a price. I would like to see a EXTRA dropdown to give it a title of Zone 1
Zone 2 Zone 3

ETc....

[whitarnold]

Roof Deck has lands and grooves like siding on a house..... Where the grooves actually go down and touch the joist - they will be welded or shot down with Powder Actuated Fasteners...4 times in 36 inches OR 5 times in 36 inches ETC......

[mcmahobt]

Okay, this is starting to make more sense.

My (hopefully) last question is: Since square footage is two dimensional, what will be your second dimension within this process? You have your joist spacing, which is always 3' wide, and then what? Your deck spacing? If that is the case, this may also be an optimization problem, since square footage can be achieved through a combination of LxW measurements.

[mcmahobt]

Disregard end of my last post, I see now that you are entering in your JOIST SPACING, which would eliminate the optimization. This seems to make sense now. I'll put in some work and get back to you.

[whitarnold]

I think of it like this (AND I THINK WE NEED TO ENTER A LENGTH AND WIDTH, NOT A SQUARE FOOT).....A ceiling has rafters (Joist) and they run north and south...then the deck will run east and west.....the deck is always 36" wide but may be different lengths... it might cover 4 joist or 6 joist....?????? Everywhere it touches a joust then it needs to be "shot down" with so many fasteners (4/36) etc..... However different fasteners cost different amounts..
one roof may have 4 zones and each zone have a different fastening pattern depending on wind uplift....

[mcmahobt]

So as I'm working through this, it seems we need to know one of two things.

1) What is the total width of the sectional area
2) What is the total length of the Joist

This is because you may be able to select 4' - 0" Joist spacing, but that doesn't tell you how many are needed. So if you input 160 sq. ft., what is the total width (4' x "X joint spacings") that is needed, which will then give you the total length of the joist needed. From there, you should be able to calculate the rest of the pricing as needed.

If you are given Square Footage and a Length, or Square Footage and a Width, the other can be calculated.

Essentially we need the total working area (in L x W format) to be able to assign values.

[whitarnold]

Yes...... Like ZONE 1 is 180'-0" Wide and we know that joist spacing is 6'-0" so that is 180/6 = 30 Deck will cross joist 30 times and be attached (4/36) four times each time it crosses the joist.
Then we need Length of ZONE 1 and that will tell us how many times it is going to be divided by 36" (one pc of deck).....
Now we have a total number of fasteners and a total price...... ALSO we will now have a SQUARE FOOT of ZONE 1 and an AVERAGE PRICE PER SQUARE FOOT????

[mcmahobt]

Yep, so the workbook I have running is matching what you just said exactly. If you have EITHER the length to enter, OR the total square footage (because dividing sq. ftg. by the width of 180' will give you the length), everything else will automatically calculate. Can you give me an example figure and I will see if it works?

[mcmahobt]

Take a look at the attached workbook and see if the resultant values look correct.

A little info about the calculation:

1) If cell C3 and D3 is populated, those values are used for the calculation regardless of what is in B3. If B3 is populated, and EITHER (but not both) C3 or D3 is empty, the calculations will still run. If BOTH C3 and D3 are empty (even if B3 is populated), no results will be given.

2) Cells B3:G3 are user input/dropdown selection cells.

3) Cells E3:J3 rely on a "Lookup Table" that is found on the Settings tab. Be sure to preserve the formatting of the cells within those lookup tables.

Hopefully this works out for you.

[whitarnold]

Do you have a PDF Viewer and I can send you a drawing with all the data that you will need to input.... PM me for email address!