useing excel to add feet and inchs like so 1' 2 "

[Guest]

I can't figure out how to do math forumlas useing feet and inchs taken off shop drawings. i am useing office 2000. i dont see any format for feet ' or inches "
If you Know HOW PLEASE post. Thankyee

 

[Norman Harker]

Hi Iron Man 77!

See:
Chip Pearson:
http://www.cpearson.com/excel/fractional.htm

 

[AlfD]

Hi!

Feet and inches keep cropping up.

The following user-defined functions will (if you are careful: no erro
traps or bells or whistles) add or subtract feet and inches.
Put the code below in a module and call it using code such a
=AddFtIns(A1,A2) to add the amounts in A1 and A2.

Remember: the inputs and the outputs are strings. If you want to d
more than just add or subtract the inputs, it is the values "feet" an
"inches" in these functions which hold the key to values.

Public Function AddFtIns(a As String, b As String) As String
' Data must be entered in worksheet as xx'yy" (no spaces)
'where xx (or just x) is the no. of feet and yy (or just y) is the no
of inches.
' If feet is zero, enter 0'yy"
'Result is a string (of course)

Dim F1, F2 As String ' Feet for a & b
Dim I1, I2 As String 'Inches for a & b

F1 = Mid(a, 1, WorksheetFunction.Find("'", a, 1) - 1)
F2 = Mid(b, 1, WorksheetFunction.Find("'", b, 1) - 1)
I1 = Mid(a, Len(F1) + 2, Len(a) - Len(F1) - 2)
I2 = Mid(b, Len(F2) + 2, Len(b) - Len(F2) - 2)
feet = Val(F1) + Val(F2)
inches = Val(I1) + Val(I2)
feet = feet + Int(inches / 12)
inches = inches Mod 12

AddFtIns = feet & " ft " & inches & " in"

End Function


Public Function SubtractFtIns(a As String, b As String) As String
' Data must be entered in worksheet as xx'yy" (o spaces)
'where xx is the no. of feet and yy is the no. of inches.
' If feet is zero, enter 0'yy"
'Result is a string (of course)

Dim F1, F2 As String ' Feet for a & b
Dim I1, I2 As String 'Inches for a & b

F1 = Mid(a, 1, WorksheetFunction.Find("'", a, 1) - 1)
F2 = Mid(b, 1, WorksheetFunction.Find("'", b, 1) - 1)
I1 = Mid(a, Len(F1) + 2, Len(a) - Len(F1) - 2)
I2 = Mid(b, Len(F2) + 2, Len(b) - Len(F2) - 2)

feet = Val(F1) - Val(F2)
inches = Val(I1) - Val(I2)
If inches < 0 Then
inches = inches + 12
feet = feet - 1
Else:
feet = feet + Int(inches / 12)
inches = inches Mod 12
End If
SubtractFtIns = feet & " ft " & inches & " in"

End Function


I haven't tested it in all conceivable circumstances, but be assured i
doesn't deal with negative inputs or fractions of inches.
The fractions bit is entirely successful in worksheet function
(mod(17.3,12) = 5.3) but not in VBA where, for some reason I haven'
yet fathomed, the subtraction copes with decimal fractions of inche
and the addition doesn't.... And 17.3 Mod 12 gives 5.

Improvements and explanations welcome!

Al

 

[AlfD]

Just an addendum. The attached does the job of multiplying ft and inche
to get areas. Subject to all the same caveats on error-checking etc.

Public Function MultiplyFtIns(a As String, b As String) As String
' Data must be entered in worksheet as xx'yy"
'where xx is the no. of feet and yy is the no. of inches.
' If feet is zero, enter 0'yy"
'Result is a string (of course)

Dim F1, F2 As String ' Feet for a & b
Dim I1, I2 As String 'Inches for a & b

Dim SInches, SFeet As Single 'S denotes sq.....
F1 = Mid(a, 1, WorksheetFunction.Find("'", a, 1) - 1)
F2 = Mid(b, 1, WorksheetFunction.Find("'", b, 1) - 1)
I1 = Mid(a, Len(F1) + 2, Len(a) - Len(F1) - 2)
I2 = Mid(b, Len(F2) + 2, Len(b) - Len(F2) - 2)


SInches = (12 * Val(F1) + Val(I1)) * (12 * Val(F2) + Val(I2))
SFeet = Int(SInches / 144)
SInches = SInches Mod 144
MultiplyFtIns = "Multiply: " & SFeet & " sq.ft. " & SInches & " sq
in."

End Function

Al