INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: SUM multiple feet'-inches" entries in one cell

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:

CODE --> BASIC

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

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

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

CODE --> vb

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:

CODE --> vb

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/

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

Quote:

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/

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

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

RE: SUM multiple feet'-inches" entries in one cell

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close