MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Real Challenge

Posted by Jeremy on October 31, 2001 1:55 PM

Two things:
1. How do you get Excel to display degrees.minutes.seconds?

2. This is the hard one. On a single, flat-bed trailer (18-wheeler), the dimensions are 50 feet long, 8 wide, and 8.5 feet tall. I'm trying to program excel to tell me whether or not a piece of pipe will fit within those dimensions. For example, if I have an 84" piece of pipe (7' in diameter) with 4 bends, how do I program excel to tell me whether or not that particular piece of pipe will fit onto a standard trailer? There are multiple ways of positioning a piece of pipe onto a truck. What I'm looking for is a formula that will calculate the smallest dimension of that pipe (i.e., the smallest amount of space that pipe will take up based on the dimensions of that piece). Any takers?

Posted by Mark W. on October 31, 2001 2:07 PM

Clarification needed on Challenge #2

You said, I have an 84" piece of pipe (7' in

Did you mean a pipe 84" in length and 7' in
diameter? If so, then doesn't the length
equal the diameter... how do you bend a pipe
like that? If 84" isn't the length, then
what is?

Posted by Qroozn on October 31, 2001 2:17 PM

I worked out how to determine the diagonal measure of the truck. Is that what you want?
what ways can pipe go on a truck? can they go diagonally across the base?, can they go from the top of one end to the botom of the opposite corner?

IF its 84" with four bends does this mean that it is 21" long?

Posted by Damon Ostrander on October 31, 2001 3:00 PM

Hi Jeremy,

Regarding the first question, here are a couple simple VBA function macros that you can use to convert a value in degrees or radians to a text value in I don't believe there is a way to do this with built-in formatting functions.

Function ToDegMinSec(Degrees As Double) As String

' Converts a value from degrees to Deg.Minutes.Seconds (text)

Dim Deg As Integer
Dim dMin As Double
Dim Min As Integer
Dim Sec As Integer

Deg = Fix(Degrees)
dMin = Abs(Degrees - Deg) * 60
Min = Int(dMin)
Sec = Int((dMin - Min) * 60)

' ToDegMinSec = Deg & "." & Format(Min, "00") & "." & Format(Sec, "00")
' Use this instead if you prefer Deg MM' SS" format
ToDegMinSec = Deg & " " & Format(Min, "00") & "' " & Format(Sec, "00") & """"

End Function

Function ToDegMMSS(Radians As Double) As String
' Use this one for radians
Const RtoD = 180 / 3.14159265358979
ToDegMMSS = ToDegMinSec(Radians * RtoD)
End Function

Regarding the second question, here are a few questions for clarification:

1. Do you want the absolute minimum size volume that the pipe will fit into, or a reasonable one for a trailer? For example, a straight piece of small-diameter pipe over 15.3' long can fit onto a trailer that is 10' x 8' x 8.5', but this requires running it from one corner of the box to the opposite corner, rather than laying it on the bed of the truck. Thus this would get the pipe onto the trailer but would not allow you to get any more pipes on, as you would if you were to lay them flat on the bed.

Observation: for a complex set of bends, it will still be easy to find a volume that the pipe will fit into. I don't believe it will be easy to find the absolute minimum volume that it will fit into. As a result you can get a yes/no answer as to whether it will fit on the truck, and you can be sure it will fit if you get a yes answer, but if you get a no answer you cannot be sure that it will not fit.

2. How do you plan to specify the bends? Are they right-angle bends, and can they be in any place in the pipe and go in any arbitrary direction?

Observation 1: if you specify the positions of all the ends and elbows in x-y-z coordinates, then this makes the problem much easier. In this case all you have to do to find a volume it will fit into is to look at maximum differences between x,y and z coordinates to get the length, width, height (not necessarily in that order), plus the pipe diameter in each direction.

Observation 2: if the bends have a large radius, this makes the problem a bit more difficult, especially if the bends are in two directions at once (like a spiral).

Happy computing.


Posted by Jeremy on November 01, 2001 4:29 AM

Clarification for Everyone

Straight pieces of pipe are easy. I'll mainly be dealing with diameters of 60" and above. The problem I'm encountering deals with multi-piece bends (several sections welded together to form a bend or an "elbow"). The problem I'm faced with is that if an elbow goes outside the dimensions of the truck (50'X 8.5'X 8') I have to order a different type of truck. Elbows can be any degree (i.e., A 4-piece bend might comprise an 80 degree angle). Each leg of the elbow is the same diameter. I have separate lengths for each leg of the elbow if that will help. Does that help you guys?

Posted by Damon Ostrander on November 02, 2001 8:22 AM

More clarification still needed.


Your clarification was helpful, but leaves an important question unanswered. You mentioned that bends can be "any degree," although I assume there is some maximum angle (are bends larger than 90 degrees allowed?). But you didn't mention whether they can be in any direction. For example, can a pipe have a 12' section followed by a 45 deg bend to the right, followed by another 12' section followed by a 90 bend upward, followed by ..., etc. This bending "out of plane" has to be specified precisely. It is not enough to say that the pipe has 3 bends of angles 45, 90 and 30 degrees. The places where the bends occur must be specified as well as the 3-dimensional DIRECTIONS that they bend.