Feet & Inches Formula

**Phrite** · 06-21-2007, 10:12 AM

I need some help figuring out a way to do this.

I am making an excel spreadsheet that auto fills in a lot of items for a construction company. One of the most important ones is the Roof Pitch.

1/12 pitch is 1 inch rise every 12 inches.
2/12 pitch is 2 inch rise every 12 inches.

I would like it to auto calculate this and add it to the over all height of the building.

Example:

A house is 20 feet wide with a 2/12 pitch.
Since the Ridge is in the center we divide the Width in half.

So a 2/12 pitch over a span of 10 feet is 20 inches

any help i can get on this would be appreciated.

**oldchippy** · 06-21-2007, 10:48 AM

Try this with your width in A1 (20) and your pitch in B1 (2), put this in C1

Please Login or Register  to view this content.

Result = 1 feet 8 inches

**Phrite** · 06-21-2007, 04:18 PM

that was hard for me to explain, but you hit it dead on. i really appreciate it.

great forums by the way. looking forward to being here for a while.

Phrite

**oldchippy** · 06-21-2007, 04:27 PM

Glad to hear it worked for you (hit the roof nail on the head)- thanks for the feedback

**Phrite** · 06-21-2007, 04:34 PM

its giving me an invalid name error.

When i run the code evaluation the only thing that is showing up is the like the example below.

Please Login or Register  to view this content.

i set this up as per your example with width in A1 (20) and pitch in B1 (2),and i put this in C1.

**oldchippy** · 06-21-2007, 04:39 PM

Sorry I forgot to say with the CONVERT function you need to load the Analysis Tool Pak

http://office.microsoft.com/en-gb/ex...277241033.aspx

**daddylonglegs** · 06-21-2007, 07:17 PM

Does the above give the correct answer? If A1 is 20 and B1 is 4 shouldn't the answer be 40 inches or 3 feet 4 inches? Suggested formula gives me 3 feet 3 inches.

If you want the answer in just inches, i.e. 40

=A1*B1/2

or if you want the answer as "3 feet 4 inches" try

=INT(A1*B1/24)&" feet "&MOD(A1*B1/2,12)&" inches"

**Phrite** · 09-21-2007, 03:40 PM

sorry for not getting back to you guys quickly.

when i post the following code it does exactly what it is supposed to. well almost.

Please Login or Register  to view this content.

it does figure the pitch. but it dosnt add it to the over all wall height.

example
i have the wall height in cell I12 showing 10 feet
i have the above code in cell I11
i have the Width in cell C12 showing 40 feet
and i have the pitch in cell F11 showing 2

it correctly shows 3 feet, 3 inches. but it should be adding it to the overall wall height showing 13 feet, 3 inches.
what do i need to do, or where do add the code to do this. please help. again thank you for the help.

**daddylonglegs** · 09-22-2007, 12:56 PM

As I pointed out above, that formula doesn't give the correct answer. If the pitch is 2 and the width 40 then shouldn't that give a result of 40 inches, which is 3 feet 4 inches?

If you use my suggested formula above, modified to include the height, i.e.

=INT(F11*C12/24)+I12&" feet "&MOD(F11*C12/2,12)&" inches"

this should give you 13 feet 4 inches

[note: the figure in 112 would need to be a decimal number so if the height is 10 ft 6 inches it would need to contain the figure 10.5]

**martindwilson** · 09-23-2007, 09:31 AM

ok try this attatched example (version 2.)amazing what a few hours sleep does). does most of whats been mentioned above.but works out every thing in inches then reverts to feet/inches,removing the need to put in 10.5 feet instead of 10ft 6 ins one thing that does happen tho is when you enter a pitch of 2/12 it auto calculates it to 1/6. thats ok tho. This will work for other pitches e.g, 1/10 if needed
i kept things to seperate columns for easiness, you could join the results i suppose to give say 13 ft 10 in one cell
formulas used
g3=INT((((C3*12)+D3)+(((A3/2*12)+B3)*E3))/12) calculates feet of total height
h3=MOD((((C3*12)+D3)+(((A3/2*12)+B3)*E3)),12) calculates remaining inches of total heigh (format cell as fraction'up to two digits')
a3= the width(feet)
b3=the width inches
c3= the orginal height(feet)
d3= the orginal height(inches)
e3 pitch in format x/y as fraction(format cell as fraction'up to two digits')

p.s.about time you guys across the 'pond' went metric