What has become almost second nature using a framing or speed square has me stumped doing the same thing in Excel which I’m new to. Hip ,valley rafters and jacks. The formulas I’m inputting result as if the waist calculation is automatic. About a variable foot and a fraction too long. All in all not a bad thing when it comes to estimating but I’d like to print a copy for the job site too. Why do the math twice?
So what’s the format? Where dose 16.97″ land in the calculation and should I have broken it down using more cells? Like a separate cell for the 1-16th” ridge growth.
Common rafter rise & run are separate in the prime details box w/ sheathing grade etc.
Best to you and yours, Chris.
EM>
Replies
Man, it's been YEARS since I tried to do any calcs like that in a spreadsheet. I don't even know if I still have any of she spreadsheets around, but I'll look for 'em Monday when I get to work.
It might help if you post an example of what you're trying to do so we could look at it. From your description I have no idea what you've done or what problems you're having.
Sorry I was vague, its just that I got myself confused with the text string. Parentheses left, Parentheses right,SQRT*?pitch factor. I,ve got it down for common rafters not including the subtracting tails ridge & fascia thickness. Thats done on the job site. Worked on it today some and may have narrowed it down using CAD. But I have a feeling that I'm going the long way around at this. As it stands now, I in put RUN & SOFFIT. One horizontal row for each 1/2†of pitch. Working laterally, [cell A ]is the pitch label [4"]. I left “D†blank. So basically I have a whole chart to do 1/4th of a hip roof. 20 by 27 cells.
Very much would like to condense that down to one row and that's where I'm getting frustrated.
[cell B] (Common rafter @ 4†is 1.054092 FT multiplier )
[cell. C] Hip / Valley @4†is 1.452917 FT =multiplier, 16.97/12 substituting for 12/12 as the base.
[cell E] Run-FT 12
[cell F] Soffit -FT 1
[cell G]Hip Ln FT =(E+F)*C 18.879
[cell H] Val Ln FT =E+C 17.435
[cell I] Common Rafter FT =(E+F)*B 13.703
[cell J] Hip jack #1 Ft =((E+F)-2)*C 11.595
[cell K] Hip jack #2 FT =((E+F)-4)*C 7.386
Then #3, 4, 5... until I get into negative numbers which I'd like to turn off or return a value of zero.
I,ve tried; [cell L] = IF( K=>1,((E+F)-6)*C and = ((E+F)-6)*C, IF(K=>1,0). I'll do the sums manually until I can find a way to automate it.
Valley jacks? Haven't started those yet.
Thanks.
Best to you and yours, Chris.
Some say I know too much? Can you ever?
Feel lucky ???(-:I attached a spreadsheet that I wrote many years back. Unfortunately, it's danged complicated. This spreadsheet was used to produce cuttting for corner sets at the last place I worked at. So there are already jacks built into it. You can change the pitch in cell A8 and it will all be re-calculated for whatever pitch you want. This spreadsheet coveres a hell of a lot of ground. And it could be re-worked to do what you want. But you'd have to be pretty good at Excel to get it done.Have a look at it, and see if any of it makes sense. If you have any questions post 'em here and I'll see if I can help.
What we obtain too cheaply, we esteem too lightly [Thomas Paine]
Unadjusted Hip-Valley Dimensions ... The worksheet solves the angles first, then the dimensions, without rounding the values. Then I created groups of cells to calculate and round to feet-inches-fractional inches and concatenated the results.
If you can't view the formulas in that spreadsheet, some of the links in this Excel in Fractions Thread have examples of worksheets showing the cells and their formulas.
Joe Bartok
Edited 2/19/2007 9:04 am ET by JoeBartok
Thank you. I did a search at JLC but didn't find that. Excel, like any building trade is a language in its self. Egilish as a primary I,ma stil wokn' on. Thanks for the other info too.
Best to you and yours, Chris.
Some say I know too much? Can you ever?
CS,
I am not an Excel expert, but this should work.
<!----><!----> <!---->
To get the decimal equivalent of 1/16 fraction values you can try this.
Copy this to a cell.
=(MOD(B5,1)/16)*10 +INT(B5)
<!----> <!---->
Enter value in inches into cell B5 with the decimal value equal to the numerator of the 1-16 fraction. So if you enter 12.4 in cell B5, value in the cell with the formula will be 12.25
<!----> <!---->
For the negative value you can use
=IF(B7<0,0,B7)
<!----> <!---->
Enter a negative value in B7 and it will return 0, otherwise it will return the value.
<!----> <!---->
If you post your formulas or the spreadsheet maybe can add other ideas.
<!----> <!---->
lvlhd
edit: noticed you wanted to do a calc if not negative.
=IF(B7<0,(E2+F2)*B2,B7)
Edited 2/20/2007 3:38 pm ET by lvlhd
Thanks for the help.
I think I'd better admit that a refresher course in Trig. would do me some good too. Best to you and yours, Chris.
Some say I know too much? Can you ever?
Thank you. I'll examine it today with great care. If questions poop, inspire or pop-up they'll be posted here.
As far as luck goes? I'll call if I hit the Lotto.Best to you and yours, Chris.
Some say I know too much? Can you ever?
Do the calcs in hidden cells, then concantenate them into the visible one.
Or set up a printing sheet and adjust cell sizes to look like one string
SamT
Anyone who doesn't take truth seriously in small matters cannot be trusted in large ones either. [Einstein] Tks, BossHogg.
I do them on a separate sheet I labeled 'Scratch pad'. But what good is hiding the formulas if I write them wrong in the first place? See my reply to BossHogBest to you and yours, Chris.
Some say I know too much? Can you ever?
Here are the sheets that I've been working on. After you take a look you may think me the simpleton. But, as I've said I'm more familiar to the framing square than Excel.
Best to you and yours, Chris.
Some say I know too much? Can you ever?
Edited 2/22/2007 8:42 am ET by CarpentrySpecialist
What kind of file is that?I've never seen an .ODS extension. Don't know what to try to open the file with.
I’m always relieved when someone delivers a eulogy and I realize I’m listening to it. [George Carlin]
I can't open the file either. Maybe "Open Office" will do the trick?
I wouldn't worry about not getting a spreadsheet right the first (or second, or third ...) time around. Nobody was born knowing how to do math, or create a worksheet, or write Javascript. We all have to start someplace and it takes time to work all the bugs out. I've been fooling around for over fifteen years and have not yet made a "perfect" roof calculator which covers all the bases.
Perhaps these links will help:
Roof Framing and Joinery Angles: Tables of formulas, diagrams and angle calculators so you can compare notes.
Once the angles are known we have to put them to work solving dimensions. For example, Calculating Hip and Valley Jacks ... different approaches to solving jack rafters. I've attached a sketch of my resolution using the Sheathing or Plywood angle. There are other threads in this forum and JLC discussing bastard Hips-Valleys.
Joe Bartok
Thanks Joe, its plugging in the numbers in the right order on the formula line is where I get into trouble. My perfect roof calculator has been the framing square, a piece of scrap wood and a pencil. 15 Years? Thanks again for letting me pick your brain.Best to you and yours, Chris.
Some say I know too much? Can you ever?
Sorry, that was OpenOffice.org version of Excel. I deleted it. My online computer is different that the one I use for work which I keep offline. Got kids? I do and do not want my office files to go poof, get hacked, etc.
Anyway, you and the others have been a great help. I've worked out these two text strings so only rise, run & over hang need to be plugged in to get the hip & valley. Can't believe I've been a total dunce about it before.
HIP RAFTER=SQRT((POWER(((RUN-in-FT*(16.97/12))+(SOFFIT-in-INCHES/12);2)+(POWER((RISE-{?in12}/12);2))))
VALLEY RAFTER=SQRT((POWER((RUN-in-FT*(16.97/12));2)+(POWER((RISE{?in12}/12);2))))
I have a feeling that this thread will pop up in future searches.Best to you and yours, Chris.
Some say I know too much? Can you ever?
Looks ilke that should work, but I didn't test it. A couple of things come to mind that might help. First - You can use the "^" symbol instead of the POWER function. Like this formula: 9^2 would be 9 to the 2nd power. Second - You can divide by the square root of 2 rather than multiplying by 16.97/12.Third - I like to break calcs down into more cells. It makes the formulas easier for me to read and/or wrap my brain around. Like I'd add the horizontal runs together in one cell, then reference that cell in the main formula.
There’s no rejection in life quite like a cancelled shrink appointment.
Using POWER is how I was able to wrap my brian around it finally. Worked it out in different cells then combind them. Didn't know ^ worked for anything so you made me smarterer. I'll use it in the future w/ sqrt2. 16.97 is a framing square job site basic that I've relied on for a long time.
Thanks BossHog.Best to you and yours, Chris.
Some say I know too much? Can you ever?
BossHog has a good suggestion; break down the formulas into more cells. I not only break down the formulas but add a text description of the calculation in the adjacent cell ... helps me remember what I did if I want to add or delete formulas five years down the road.Joe Bartok
My idea of the "perfect" calculator has changed with time. One spreadsheet I worked on for years ... began with the major dimensions such as you are doing now. I kept adding sketches and calculations for the different joints.
Finally I gave up and never did get all the possible joints and intersections into the spreadsheet. There are just too many bases to cover. Besides, I generally work with logs and don't know the tenon dimensions and housing depths (fifteen inches nominal diameter on a drawing doesn't tell me much ...), so I can't do detailed joinery calcs in advance. Nowadays I print the angles generated by this Framing and Joinery Angle Calculator and do the final calcs on-site with a scientific calculator.
Joe Bartok
Edited 2/22/2007 12:18 pm ET by JoeBartok
Edited 2/22/2007 12:23 pm ET by JoeBartok
Edited 2/22/2007 12:23 pm ET by JoeBartok
Your right that there are a lot of bases to cover. I've used those little red triangle notes as I've gone along. With a little reading & help from y'all here, the latest version has the sheet reduced to about 5 cells wide and 30 tall. Or one sheet of paper to print not 10. Using the "IF" function I can switch from equal hip to gable roof to get lumber, sheathing, shingle and what ever sits above the top plate.
Again thanks.Best to you and yours, Chris.
Some say I know too much? Can you ever?
To all those that put your $.02 or more into this tread and for your help. Thank you!
Best to you and yours, Chris.
Some say I know too much? Can you ever?