SUM multiple feet'-inches" entries in one cell 1

[4thorns]

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

 

[LiteYear]

Surely it would be easiest just to hide the working column? You could even move it to another sheet if you really want it out of sight.

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:

Function DoCalc()
  DoCalc = 1
End Function

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().

 

[4thorns]

Thanks for the reply LiteYear,
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

 

[IDS]

A UDF to do what you want is actually not a lot longer than LiteYear's outline:

Function SumFtinf(FtinRange, Optional Denom As Long = 0)
Dim cell As Range, DecSum As Double

For Each cell In FtinRange
DecSum = DecSum + FtInf2m(cell.Value2)
Next cell

SumFtinf = M2Ftinf(DecSum, Denom)

End Function

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:

Function SumFtinf(FtinRange)
Dim cell As Range, FtSum As Double

For Each cell In FtinRange
FtSum = FtSum + Feet(cell.Value2)
Next cell

SumFtinf = LenText(FtSum)

End Function

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/

 

[4thorns]

Thanks Doug. It took a minute but I've gotten it to work for one range. I named the range above the total FtinRange.
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

 

[4thorns]

Never mind Doug. Think I've Got it. Put the formula in the "total" cell, put the range above it in the ().
Amazing how simple it is!

Thanks again,
Doug

 

[4thorns]

OK. Correct me if I'm wrong...The (FtinRange) is merely a name that the script uses that is based on your range input?

 

[IDS]

OK. Correct me if I'm wrong...The (FtinRange) is merely a name that the script uses that is based on your range input?

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/

 

[4thorns]

Thanks for the info Doug and I appreciate you spending time with this. Headed to bed but I'm fairly certain that before long we'll talk again.

 

[yakpol]

Alternatively, you can write an array formula without using VBA.
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

 

[IDS]

yakpol - That to me looks like a pretty good argument for using VBA

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/extending-complex-udfs-with-simple-vba/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[drawoh]

4thorns,

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