Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Adding a Little Animation to Your Life
Converting a Range of URLs to Hyperlinks
Making the Formula Bar Persistent
Peter asked if it was possible in Excel to create a custom number format that will deal with imperial linear distances, such as inches, feet, miles, etc. The short answer is that no, this is not possible. Excel works natively in the decimal system, and many imperial measuring systems are based on other numeric systems (feet on base 12, for instance). While custom formatting can change the way that numbers are displayed, it cannot perform the conversions necessary for imperial measurements.
Your best bet is to keep the different units of whatever imperial measurement you want in different cells. For instance, a distance of 3 miles, 428 feet, and 7 inches could be kept in three cells, one for miles, one for feet, and the other for inches. You could then write the formulas necessary to convert to whatever measurement system you desire. There are also Excel add-ins available around the Internet (a quick search will find them) that can allow you to use this technique to work with linear measurements.
Another approach is to develop a custom function or macro that would convert a value into a linear measurement and display it as text. You couldn't use the result in math functions, but it may give you want you want for your workbook. Consider, for example, the following simple macro:
Function N2MYFI(x) As String
Dim x1 as Long
Dim FinalAnswer As String
x1 = x
Distances = Array(63360, 36, 12, 1)
FinalAnswer = ""
For Each Item In Distances
FinalAnswer = FinalAnswer & " " & Int(x1 / Item)
x1 = x1 - Item * Int(x1 / Item)
Next
N2MYFI = Trim(FinalAnswer)
End Function
This function returns four numbers, in a string, that represent the number of miles, yards, feet, and inches (MYFI) in a raw value. It is assumed that the value fed to the function is in inches, such as the following:
=N2MYFI(100)
This returns the string "0 2 2 4", which means there are 2 yards, 2 feet, and 4 inches in 100 inches. The function could easily be changed to return the values in any format desired.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3137) applies to Microsoft Excel versions: 97 2000 2002 2003
Save Time and Money! Many people need to keep track of employee time, but don't know where to start when it comes to creating a spreadsheet. Here's a way to save time, effort, and money with ready-to-use timesheet templates.