SUM multiple feet'-inches" entries in one cell
SUM multiple feet'-inches" entries in one cell
(OP)
I found this code somewhere online. I forget who the author was and apologize but it works very well.
I have a column with several measurements in Feet"-Inch" format. The corresponding cells in the column next to it convert the feet and inches to decimals. At the bottom I have a cell that sums the decimals. I have another cell that converts the total back to feet and inches. What I need to do is have a cell that..
1: converts all lengths to decimals at once
2: sums them
3: converts the total back to feet and inches
Basically I'd like to eliminate the need for the extra column.
Any thoughts?
Thanks,
Doug
Public Function feet(LenString As String)
Dim FootSign As Integer
Dim InchSign As Integer
Dim SpaceSign As Integer
Dim FracSign As Integer
Dim InchString As String
Dim Word2 As String
' Copyright 1999, 2005 MrExcel.com
LenString = Application.WorksheetFunction.Trim(LenString)
'The find function returns an error when the target is not found
'Resume Next will prevent VBA from halting execution.
On Error Resume Next
FootSign = Application.WorksheetFunction.Find("'", LenString)
If IsEmpty(FootSign) Or FootSign = 0 Then
' There are no feet in this expression
feet = 0
FootSign = 0
Else
feet = Val(Left(LenString, FootSign - 1))
End If
' Handle the case where the foot sign is the last character
If Len(LenString) = FootSign Then Exit Function
' Isolate the inch portion of the string
InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 2))
' Strip off the inch sign, if there is one
InchSign = Application.WorksheetFunction.Find("""", InchString)
If Not IsEmpty(InchSign) Or InchSign = 0 Then
InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))
End If
' Do we have two words left, or one?
SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
' There is only one word here. Is it inches or a fraction?
FracSign = Application.WorksheetFunction.Find("/", InchString)
If IsEmpty(FracSign) Or FracSign = 0 Then
'This word is inches
feet = feet + Val(InchString) / 12
Else
' This word is fractional inches
feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12
End If
Else
' There are two words here. First word is inches
feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12
' Second word is fractional inches
Word2 = Mid(InchString, SpaceSign + 1)
FracSign = Application.WorksheetFunction.Find("/", Word2)
If IsEmpty(FracSign) Or FracSign = 0 Then
' Return an error
feet = "VALUE!"
Else
If FracSign = 0 Then
feet = "VALUE!"
Else
feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12
End If
End If
End If
End Function
Public Function LenText(FeetIn As Double)
' This function will change a decimal number of feet to the text string
' representation of feet, inches, and fractional inches.
' It will round the fractional inches to the nearest 1/x where x is the denominator.
' Copyright 1999 MrExcel.com
Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.
NbrFeet = Fix(FeetIn)
InchIn = (FeetIn - NbrFeet) * 12
NbrInches = Fix(InchIn)
FracIn = (InchIn - NbrInches) * Denominator
Numerator = Application.WorksheetFunction.Round(FracIn, 0)
If Numerator = 0 Then
FracText = ""
ElseIf InchIn >= (11 + (31.4999999 / 32)) Then
NbrFeet = NbrFeet + 1
NbrInches = 0
FracText = ""
ElseIf Numerator = Denominator Then
NbrInches = NbrInches + 1
FracText = ""
Else
Do
' If the numerator is even, divide both numerator and divisor by 2
If Numerator = Application.WorksheetFunction.Even(Numerator) Then
Numerator = Numerator / 2
Denominator = Denominator / 2
Else
FracText = " " & Numerator & "/" & Denominator
Exit Do
End If
Loop
End If
LenText = NbrFeet & "'-" & NbrInches & FracText & """"
End Function
I have a column with several measurements in Feet"-Inch" format. The corresponding cells in the column next to it convert the feet and inches to decimals. At the bottom I have a cell that sums the decimals. I have another cell that converts the total back to feet and inches. What I need to do is have a cell that..
1: converts all lengths to decimals at once
2: sums them
3: converts the total back to feet and inches
Basically I'd like to eliminate the need for the extra column.
Any thoughts?
Thanks,
Doug
Public Function feet(LenString As String)
Dim FootSign As Integer
Dim InchSign As Integer
Dim SpaceSign As Integer
Dim FracSign As Integer
Dim InchString As String
Dim Word2 As String
' Copyright 1999, 2005 MrExcel.com
LenString = Application.WorksheetFunction.Trim(LenString)
'The find function returns an error when the target is not found
'Resume Next will prevent VBA from halting execution.
On Error Resume Next
FootSign = Application.WorksheetFunction.Find("'", LenString)
If IsEmpty(FootSign) Or FootSign = 0 Then
' There are no feet in this expression
feet = 0
FootSign = 0
Else
feet = Val(Left(LenString, FootSign - 1))
End If
' Handle the case where the foot sign is the last character
If Len(LenString) = FootSign Then Exit Function
' Isolate the inch portion of the string
InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 2))
' Strip off the inch sign, if there is one
InchSign = Application.WorksheetFunction.Find("""", InchString)
If Not IsEmpty(InchSign) Or InchSign = 0 Then
InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))
End If
' Do we have two words left, or one?
SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
' There is only one word here. Is it inches or a fraction?
FracSign = Application.WorksheetFunction.Find("/", InchString)
If IsEmpty(FracSign) Or FracSign = 0 Then
'This word is inches
feet = feet + Val(InchString) / 12
Else
' This word is fractional inches
feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12
End If
Else
' There are two words here. First word is inches
feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12
' Second word is fractional inches
Word2 = Mid(InchString, SpaceSign + 1)
FracSign = Application.WorksheetFunction.Find("/", Word2)
If IsEmpty(FracSign) Or FracSign = 0 Then
' Return an error
feet = "VALUE!"
Else
If FracSign = 0 Then
feet = "VALUE!"
Else
feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12
End If
End If
End If
End Function
Public Function LenText(FeetIn As Double)
' This function will change a decimal number of feet to the text string
' representation of feet, inches, and fractional inches.
' It will round the fractional inches to the nearest 1/x where x is the denominator.
' Copyright 1999 MrExcel.com
Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.
NbrFeet = Fix(FeetIn)
InchIn = (FeetIn - NbrFeet) * 12
NbrInches = Fix(InchIn)
FracIn = (InchIn - NbrInches) * Denominator
Numerator = Application.WorksheetFunction.Round(FracIn, 0)
If Numerator = 0 Then
FracText = ""
ElseIf InchIn >= (11 + (31.4999999 / 32)) Then
NbrFeet = NbrFeet + 1
NbrInches = 0
FracText = ""
ElseIf Numerator = Denominator Then
NbrInches = NbrInches + 1
FracText = ""
Else
Do
' If the numerator is even, divide both numerator and divisor by 2
If Numerator = Application.WorksheetFunction.Even(Numerator) Then
Numerator = Numerator / 2
Denominator = Denominator / 2
Else
FracText = " " & Numerator & "/" & Denominator
Exit Do
End If
Loop
End If
LenText = NbrFeet & "'-" & NbrInches & FracText & """"
End Function





RE: SUM multiple feet'-inches" entries in one cell
If that's not satisfactory, then sure, you could write a custom function to do the lot. In the Excel Visual Basic Editor go to the "Insert" menu and choose "Module". In the editor that appears, add a function like:
CODE --> BASIC
Try it out by setting a cell's contents to "=DoCalc()".
Now you just need to change the body of the function to loop through all the cells you want to convert, passing their contents to feet(), accumulating the result, and finally passing the sum to LenText().
RE: SUM multiple feet'-inches" entries in one cell
Hate to say it but i forgot about this post. Mainly because I took your advice and left it alone. I have a workbook with 10 sheets. Each with 3 columns
of 28 entries. It was kind of a pain originally to set up a data sheet to provide the info to all the sheets but it was already done. I was making a different version of this workbook and didn't want to set up another huge data sheet thus the OP. In the end I set it up the same way.
Another reason I didn't use your approach is....Quite frankly I wouldn't know how to script it! Still in the learning phase and trying to squeeze in the time to
figure out vba. Pluggin away tho!
Thanks again,
Doug
RE: SUM multiple feet'-inches" entries in one cell
CODE --> vb
That's using my own functions that convert ft and fractional inches to metric and back again. Converting that to your functions, and assuming you don't need the "denom" argument would be:
CODE --> vb
If you copy and paste that in any convenient code module (can be the same one as your two existing functions, but doesn't have to be), it should work.
As for whether it is worth doing, I like UDFs, especially simple ones like this because:
- They make the spreadsheet more robust against tampering.
- They don't take long to set up (after a bit of practice).
- Once set up, they can save a lot of time.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: SUM multiple feet'-inches" entries in one cell
When I copied it to the next cell it reads off the first. Do I have to name each range above each total in order for
this to work or am I missing something?
Doug
RE: SUM multiple feet'-inches" entries in one cell
Amazing how simple it is!
Thanks again,
Doug
RE: SUM multiple feet'-inches" entries in one cell
RE: SUM multiple feet'-inches" entries in one cell
That's right, you use it just like the Sum function. There are times when it is useful to name the range the function refers to, but you don't need to, and as you discovered if you copy a function with a named range it still refers to the original range (which is sometimes useful, and sometimes not).
In general, when you write a User Defined Function in VBA you give the input range(s) a name so you can refer to them in the VBA code, but when you use them you can just enter a range address in the same way as a built-in function.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: SUM multiple feet'-inches" entries in one cell
RE: SUM multiple feet'-inches" entries in one cell
Assuming feet-inch format 4'-6 5/16" and the range to sum A1:A10, type the formula
=SUM(VALUE(LEFT(A1:A10,FIND("'",A1:A10,1)-1)+VALUE(LEFT(MID(A1:A10,FIND("-",A1:A10,1)+1,10),LEN(MID(A1:A10,FIND("-",A1:A10,1)+1,10))-1)/12)))
and enter it with ctr-shift-enter keyboard combination to achieve array formula.
It is a long formula in order to eliminate all intermediate values and indexes.
Regards,
Yakpol
RE: SUM multiple feet'-inches" entries in one cell
But it's good to have the alternative.
I have now added the SumFtInf function to my Units4Excel spreadsheet, which you can download from:
http://newtonexcelbach.wordpress.com/2014/09/05/ex...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: SUM multiple feet'-inches" entries in one cell
Why do a script at all? Here are the contents of two columns, summing the feet and inch columns above...
Cell A8: =SUM(A2:A7)+FLOOR(SUM(B2:B7)/12,1)
Cell B8: =SUM(B2:B7)-12*FLOOR(SUM(B2:B7)/12,1)
--
JHG