Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Feet and Inches (Excel 2000 SR1)

    I have pages of dimensions from an architect, e.g., 38' 6" x 6' 3" - I need a formula to tell me the square feet in this room.

    Thank you in advance - again.

    Linda

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    The following user defined function will give you what you want:

    <pre>Public Function GetArea(strSize As String) As Double
    Dim strWk As String, iLen As Integer
    Dim dWid As Double, dlen As Double
    strWk = Trim(strSize)
    iLen = InStr(strWk, "'")
    dWid = Left(strWk, iLen - 1)
    strWk = Trim(Right(strWk, Len(strWk) - iLen))
    iLen = InStr(strWk, """")
    dWid = dWid + Trim(Left(strWk, iLen - 1)) / 12
    iLen = InStr(strWk, "x")
    strWk = Trim(Right(strWk, Len(strWk) - iLen))
    iLen = InStr(strWk, "'")
    dlen = Left(strWk, iLen - 1)
    strWk = Trim(Right(strWk, Len(strWk) - iLen))
    iLen = InStr(strWk, """")
    dlen = dlen + Trim(Left(strWk, iLen - 1)) / 12
    GetArea = dWid * dlen
    End Function
    </pre>

    This can be done using just formulas in the worksheet. However, it requires about eight formulas in eight cells because the formula gets too complex for Excel to handle in a single cell. If you are interested here are the formulas:

    <pre>Cell Contents
    A1 38' 6" x 6' 3"
    B1 =VALUE(LEFT(A1,SEARCH("'",A1)-1))
    C1 =TRIM(RIGHT(A1,LEN(A1)-SEARCH("'",A1)))
    D1 =B1+VALUE(TRIM(LEFT(C1,SEARCH("""",C1)-1)))/12
    E1 =TRIM(RIGHT(C1,LEN(C1)-SEARCH("x",C1)))
    F1 =VALUE(LEFT(E1,SEARCH("'",E1)-1))
    G1 =TRIM(RIGHT(E1,LEN(E1)-SEARCH("'",E1)))
    H1 =F1+VALUE(TRIM(LEFT(G1,SEARCH("""",G1)-1)))/12
    I1 =D1*H1
    </pre>
    Legare Coleman

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    Um - I totally respect your answer Legare, but wouldn't this be simpler?
    =(((38*12)+6)*((6*12)+3))/12

    Unless of course this is a repetitive job in which case building a function is the way to go!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    I believe that her message said that she had "Pages" of dimensions. Besides, are you looking for the simple answer or the fun answer? <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    .../144 methinks.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    Thanks for the reply. I know about the /144 part - the problem is there are several occasions where, for example, the dimensions of a room might be:

    (38' 6" x 6' 3") = (9' x 6' 9")

    Now what do I do? I can do it, of course, but I sure would like a global something so no matter what dimension I type in Excel will figure out the square footage for me in one swift stroke.

    Linda

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    This is terrific - I just hope I'm correct in assuming I write this as a macro?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    The top one is a macro. You put it into a VBA module, then if the dimensions are in A1, you would enter something like this in the cell where you want the area:

    <pre>=GetArea(A1)
    </pre>


    The bottom half are spreadsheet formulas. You just copy the formulas and paste them into the cells indicated. Then if the dimensions are in Column A, just copy the formulas down the columns.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    You are a prince among men. Thank you, thank you.

    I obviously need to learn VBA and macro use - any recommendations?

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    I can't compete with you in the FUN department <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> - so I was trying for <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> simple! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    You are in one of the best places to learn. Just ask about anything you don't understand.
    Legare Coleman

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    Okay - here goes.

    I probably wasn't very clear when I posted my first question. This is what I should have told everyone:

    The information I get from the architects looks like this:

    (38' 6" x 6' 3") + (9' x 6")

    How do I enter this information and where do I put what parts of it?

    I've made a spreadsheet - it's attached. What do you think?

    Linda
    Attached Files Attached Files

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    Well, that is a bit different! See if the attached workbook will do what you want.
    Attached Files Attached Files
    Legare Coleman

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    Can't wait to see what you've sent me but I get an "unable to launch application. Appliation not found." message when I try to open it. Saved it to my downloads file - tried to open it from there. got the "cannot find the file . . ." message. HELP!

    Linda

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Feet and Inches (Excel 2000 SR1)

    I don't know what might be happening to you. I can click on the file and it opens fine. I used Excel 97 to create the file, but that should not be a problem if you have Excel 2000.

    The first message that you gave indicates that your computer can not find the Excel program if that is what is linked to the .xls file extension. The second message says that it can find the .xls file, and this usually indicated that there is a problem with the definition of the .xls file type link.

    I would suggest two things:

    1- Rename the file so that it is less than 8 characters and contains no blanks.

    2- Try starting Excel and then use the File/Open menu to open the file.

    One more question. Can you click on the file that you uploaded and open it?
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •