Working With Imperial - Useful Excel Function Found But Help Needed ... 5

[NWildblood]

Hi, any help gratefully received: I am working in imperial (Miles/Yards) and have found that it is not possible create a custom number format that will deal with imperial linear distances: yards and miles. Put simply, I need to determine the difference in Miles and Yards between e.g.

Mileage Work Order
MM.YYYY
25.1727


Mileage System
MM.YYYY
24.0880


There are 1760 Yards in a Mile.

I want to display the difference as
1.0847 (1 mile and 847 Yards)

Workings: 24.0880 + 0880 = 25.000
1727 + 0880 = 2607 Yards
= 1.0847

I have found a custom function that converts a value into a linear measurement and display it as text, which is potentially a really useful lead to a sollution:

_________________________________
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 0 miles, 2 yards, 2 feet, and 4 inches in 100 inches. I can work with this sort of output, and the function works fine, however, I cannot seem to redesignate the array to work in combinations of Miles and Yards.

Ideal format will be Miles.Yards - to four points - as 1 mile = 1760 yards

Any pointers would be *really* helpful !

Thank you



No hay nada como un buitre cabr=n como un ojo de cristal.

 

[SkipVought]

@z, I'm not contesting that.

In fact I can remember over 4 decades ago, my son was in a 6th grade math class doing metric conversion exersizes, and I asked him whether they also has English conversion exersizes. He indicated not. So I asked to have a meeting with his teacher. In the meeting, she indicated that, oh the U.S. Is going to convert to metric next year, so we don't need to worry about this old illogical English system. REALLY, in 1972 she said that!

I'm just chapped that what the OP portrayed was misleading, especially when he referred to direct arithmetic on the values in columns A & B! Maybe it's me. Just got home from a visit with relatives where I ended up in the hospital overnight with pneumonia.

 

[NWildblood]

Skip, I can take a bollocking, I have been doing this project among a load of other things, and under pressure of having very little time to turn this bit around (I did not choose the launch date, someone on a much higher pay grade did that). I have legions of track engineers out there to whom I need to display the differences of *their* records accross two systems (one, creaking, predicated on flat files that was coincidentially commissioned in 1972) - and these engineers need the differences displayed in miles and yards. To be frank I haven't had much time to work the problem through logically. I appreciate I have effectively just passed working the problem on, and haven't been altogether logical in my approach (!)... even as I posted the question on Friday PM I was anxious I may not have covered the issue properly. I hope things improve with your health. Best wishes OOS.

 

[zelgar]

I just thought of something. You might want to have some error checking in your code to verify that both numbers are in the Miles.Yards format. If any of the numbers had a value of with a decimal value greater than or equal to 1760 then you would know that there was a data entry error.

Function OOS(x As String, y As String) As Variant
Dim a, b
a = Split(x, ".")
b = Split(y, ".")
If a(1) < 1760 And b(1) < 1760 Then
    xy = 1760 * a(0) + a(1)
    yy = 1760 * b(0) + b(1)
    Final = xy - yy
    OOS = Final \ 1760 & "." & Format(Final Mod 1760, "0000")
Else
    If a(1) >= 1760 Then OOS = "ERROR..first value has Yards > 1760)"
    If b(1) >= 1760 Then OOS = "ERROR..second value has Yards > 1760)"
    If a(1) >= 1760 And b(1) >= 1760 Then OOS = "ERROR..both values have Yards > 1760)"
End If
End Function

Also, would you ever need a function to add the values? If so, you can modify the above code or 1 function could be made to perform additional functions (e.g., add, subtract, multiply or divide)

 

[SkipVought]

I monitored this thread and never posted a reply because I was confused by the lack of clarity, until your post...

OutOfShape (TechnicalUser)(OP) 15 May 15 13:52
Thank you zelgar, that is helpful - although I am struggling to apply this to show the result:

[highlight #FCE94F]CELL A1 - CELL B1 = CELL C1[/highlight]
25.1727 24.0880 1.0847
....

[highlight #FCE94F]That[/highlight] told me that your values in columns A & B were [highlight #FCE94F]Miles ONLY[/highlight], because ANYONE with a grade school education knows you cannot do arithmetic on mixed unit values.

Then I responded about 2 hours later, since I had my [highlight #FCE94F]clarification[/highlight]

SkipVought (Programmer) 15 May 15 15:46
So you want to convert some distance, (could be a distance in inches, feet, yards or miles) to Miles & Yards.

This function takes 2 arguments; the Distance and the Units of the distance as IN, FT, YD, MI. The result in the M.YYYY format is a string.

But you did do arithmetic using mixed units values, and that was TOTALLY misleading.

You continued to mislead by using the values in columns A & B directly in arithmetic

OutOfShape (TechnicalUser)(OP)18 May 15 08:45
OK, here are some results:

@SkipVought
[highlight #FCE94F]A1 - B1 = C1
25.1727 - 24.0880 = 1.0847[/highlight] EXPECTED RESULT

However, using [highlight #FCE94F]A1-B1[/highlight] I was getting 1.0149

But using =(D2MY([highlight #FCE94F]D1-E1)/1760[/highlight])conversion to yards *1760 - I was able to get 1.0847 from D1) 44303.952 - E1) 42394.88

In a second example,
[highlight #FCE94F]A2 - B2[/highlight]
90.0836 - 89.1320

I expect to get 0.1276

However, using =D2MY1([highlight #FCE94F]A2-B2[/highlight])
I am seeing 0.1675

And using =(D2MY([highlight #FCE94F]A2-B2)/1760[/highlight])
I am seeing 0.9512

In a third example
[highlight #FCE94F]A3 - B3[/highlight]
13.1337 - 13.0874

I expect to get 0.0463

However, using =D2MY1([highlight #FCE94F]A3-B3[/highlight])
I am seeing 0.0081

And using =(D2MY([highlight #FCE94F]A3-B3)/1760[/highlight])
I am seeing 0.0461

So IMNSHO, this was more than simply not being clear. You don't seem to grasp that.

 

[NWildblood]

Thanks zelgar, I will take a look at that tomorrow.

 

[SkipVought]

This will be my last post, and hopefully this will be more constructive.

I have observed, over the past 30 years, being involved with data and process conversion from one system to another, a typical user "requirement" when converting data from a manual system to an automated system, otherwise stated as converting data stored on paper to data stored in a computer: "I want it to be the SAME WAY as we had before!"

So the brow beaten computer analyze caves in and accommodates "the way it used to be."

I have seen this done dozens of times, at Vought Aircraft, Vought/Northrop Grumman, Bell Helicopter/Textron. And the result has always been sub-optimal! It eventually gets fixed, or crutched in some way to accommodate the automated system's superior capabilities that the myopic users failed to foresee.

The rule of thumb In such data conversion, is to make a separate field for each unit of measure; in this case, in your sheet, you ought to have a column for MILES and a column for YARDS. If the user absolutely wants to "see" M.YYYY, then you do a final DISPLAY ONLY TEXT VALUE that displays this mixed units value. All the other math can be simply performed with spreadsheet conversion formulas.

So column headings might be:
[tt]
MILES1
YARDS1
MILES2
YARDS2
TOTAL YARDS1
TOTAL YARDS2
MILES DIFF
YARDS DIFF
IMPERIAL (Optional display-only TEXT)
[/tt]
This would remove all doubt and ambiguity.

 

[DaveInIowa]

Since Excel will zero-suppress on either side of the decimal point, what does passing a string value of say "1.8" represent; 1 mile and 8 yards or 1 mile and 8000 yards?

 

[SkipVought]

@Dave, This M.YYYY is TEXT

Where my proposeal...
[pre]
MILES YARDS
1 8
[/pre]

=TEXT(A2+B2/10000,"#.0000")

1.0008 TEXT

 

[DaveInIowa]

My question remains; what does a TEXT value of "1.8" represent, "1.8000", "1.0008" or is it an invalid entry because it's not 4 digits to the right of the yards delimiter?

 

[SkipVought]

@Dave

That's like asking, what does 5:8 mean as time.

 

[zelgar]

Dave, since the different functions are only reading the value after the "." it would read it as 1 mile and 8 yards. If the value was 1.8000 the function would read 1 mile and 8000 yards which should be an error since it should be 4.0960.

 

[DaveInIowa]

We've been given a partial definition of the data's format. Miles to the left of the delimiter and yards to the right. My question is, should the yards segment be padded to 4 digits with zeros to the left or right of the given digits? 5:8 could be a perfectly valid representation of time if the rules are explained properly. Not given any additional information, I'd say it means 5:08 AM.

 

[SkipVought]

@Dave,

Good question as just stated.

However, the implication from the OPs posts show leading zeros to 4 places. So I'd conclude the 1.8 would be an illegal format and should be at least either 1.8000 or 1.0008 (8000 yards or 8 yards)

 

[zelgar]

With the current functions using the period to separate the Miles and Yards in the number, it would read 1.8 as 1 Mile and 8 Yards. The function would need to be modified to require there were 4 digits after the decimal. Additionally, if the value in the cell is shown as 1.0008 but the value is actually 1.0008323 the function would read it as 1 Mile and 8323 Yards.

Slight modifying the functions to get a different reading from the cell to read 1.0008323 to be 1 Mile and 8 Yards or 1.8 to be 1 Mile and 8000 Yards would be the following
Getting rid of the Split command and adding the following lines for defining a & b
a(0) = INT(x)
a(1) = INT((x - INT(x))*1000)
b(0) = INT
b(1) = INT((y - INT)*1000)

 

[DaveInIowa]

@Skip, Thank you. I was looking for someone's opinion on handling ambiguous yard segment values. Your response was what I was looking for.

Here's a conglomeration of previous ideas, error checking and meaningful names...

Public Function YardsFromMilesAndYards(milesAndYards As String, Optional delimiter As String = ".") As Variant
    Dim distance As Variant
    
    distance = Split(milesAndYards, delimiter)
    If (Not IsNumeric(distance(0))) Then
        YardsFromMilesAndYards = "Miles value is not numeric"
        Exit Function
    End If
    
    YardsFromMilesAndYards = CLng(distance(0)) * 1760
    
    If (UBound(distance) > 0) Then
        If (Not distance(1) Like "####") Then
            YardsFromMilesAndYards = "Yards value must be 4 digits"
            Exit Function
        End If
        If (CLng(distance(1)) > 1759) Then
            YardsFromMilesAndYards = "Yards value must be less than 1760"
            Exit Function
        End If
        YardsFromMilesAndYards = YardsFromMilesAndYards + CLng(distance(1))
    End If
End Function

Public Function MilesAndYardsFromYards(yards As Long, Optional delimiter As String = ".") As String
    MilesAndYardsFromYards = (yards \ 1760) & delimiter & Format(yards Mod 1760, "0000")
End Function

Public Function AddMilesAndYards(firstMilesAndYards As String, secondMilesAndYards As String, Optional delimiter As String = ".") As String
    Dim result As Variant
    Dim yards As Long
    
    result = YardsFromMilesAndYards(secondMilesAndYards, delimiter)
    If (Not IsNumeric(result)) Then
        AddMilesAndYards = result & " (secondMilesAndYards)"
        Exit Function
    End If
    
    yards = result
    
    result = YardsFromMilesAndYards(firstMilesAndYards, delimiter)
    If (Not IsNumeric(result)) Then
        AddMilesAndYards = result & " (firstMilesAndYards)"
        Exit Function
    End If
    
    AddMilesAndYards = yards + result
End Function

Public Function SubtractMilesAndYards(startingMilesAndYards As String, endingMilesAndYards As String, Optional delimiter As String = ".") As String
    Dim result As Variant
    Dim yards As Long
    
    result = YardsFromMilesAndYards(endingMilesAndYards, delimiter)
    If (Not IsNumeric(result)) Then
        SubMilesAndYards = result & " (endingMilesAndYards)"
        Exit Function
    End If
    
    yards = result
    
    result = YardsFromMilesAndYards(startingMilesAndYards, delimiter)
    If (Not IsNumeric(result)) Then
        SubMilesAndYards = result & " (startingMilesAndYards)"
        Exit Function
    End If
    
    SubMilesAndYards = yards - result
End Function

 

[NWildblood]

Thanks Dave, that is great.