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

=INT(CONVERT(ROUND((B1*A1/2)/12,2),"ft","ft"))&" feet " &INT( MOD(CONVERT(ROUND((B1*A1/2)/12,2),"ft","in"),12))& " inches"

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.

=INT(CONVERT(ROUND((B1*A1/2)/12,2),"ft","ft"))&" feet " &INT( MOD(CONVERT(ROUND((B1*A1/2)/12,2),"ft","in"),12))& " inches"

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"