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.

 

[zelgar]

Using your Function as a base, I was able to come up with the following:

Function N2MY(x) As Variant
Dim x1 As Long
Dim FinalAnswer As Variant

x1 = x
Distances = Array(1760, 1)
i = 1
FinalAnswer = 0
For Each Item In Distances
FinalAnswer = FinalAnswer + Int(x1 / Item) / i
x1 = x1 - Item * Int(x1 / Item)
i = 10000
Next

N2MY = Format(FinalAnswer, "0.0000")

End Function

 

[PeterMoran]

Hi,

You may like to checkout this site:

Link

HTH

Peter Moran

 

[NWildblood]

Thank you zelgar, that is helpful - although I am struggling to apply this to show the result:

CELL A1 - CELL B1 = CELL C1
25.1727 24.0880 1.0847

(I seem to be getting 0.0001)

Thanks again
OOS

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

 

[NWildblood]

Thanks Peter - that's exactly where I got my example from - however I can't seem to make the converstion to Miles.Yards

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

 

[SkipVought]

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.

Function D2MY(D, UNI As String) As String
'converts some (D) distance in (UNI) IN, FT, YD, MI to Miles & yards M.YYYY
'1760 YD/MI
    Const YPM = 1760
    Const FPM = 5280
    Const IPM = 63360
    
    Dim MI, YD, MILES As Long, YARDS As Long
    
    Select Case UNI
        Case "IN"
            MI = D / IPM
        Case "FT"
            MI = D / FPM
        Case "YD"
            MI = D / YPM
        Case "MI"
            MI = D
    End Select
    MILES = Int(MI)
    YARDS = (MI - MILES) * YPM
    D2MY = MILES & "." & Format(YARDS, "0000")
End Function

 

[zelgar]

OOS, When I do the calculation =A1-B1 I get a result of 1.0847 where
A1 = N2MY(45727) = 25.1727
B1 = N2MY(43120) = 24.0880
C1 = A1 - B1 = 1.0847

To be honest, I didn't think that it should work since the number provided by the N2MY Function is not really a true number (e.g., the decimals are yards, not a x miles/10,000). I would have thought that you'd need to have C1 = N2MY (45727 - 43120) to get the correct value, but it seems to calculate properly the result either way. The only difference I see is the justification (i.e., if C1 = N2MY (45727 - 43120) the data is left justified, but if C1 = A1 - B1 the data is right justified)

 

[SkipVought]

The function returns a STRING rather than a NUMBER, because it is not a true number on which any arithmetic would make any sense. It is ONLY a visual representative of miles and yards.

It would be like representing Feet and Inches in a similar format, that 1.5 feet would be 1.6 in feet.inches. How would you use 1.6 meaningfully in a arithmetic expression that would expect 1.5?

That's why Date/Time formats are DISPLAY ONLY, while the underlying values are represented as DAYS in which arithmetic is performed!

 

[SkipVought]

I modified the second argument as Optional with a default value of MI.

Function D2MY(D, Optional UNI As String = "MI") As String
'converts some (D) distance in (UNI) IN, FT, YD, MI to Miles & yards M.YYYY
'1760 YD/MI
    Const YPM = 1760
    Const FPM = 5280
    Const IPM = 63360
    
    Dim MI, YD, MILES As Long, YARDS As Long
    
    Select Case UNI
        Case "IN"
            MI = D / IPM
        Case "FT"
            MI = D / FPM
        Case "YD"
            MI = D / YPM
        Case "MI"
            MI = D
    End Select
    
    MILES = Int(MI)
    YARDS = (MI - MILES) * YPM
    D2MY = MILES & "." & Format(YARDS, "0000")
End Function

 

[SkipVought]

BTW, here's how to use this function in C1
[tt]
=D2MY(A1-B1)
[/tt]

 

[zelgar]

I don't know how my Function is working for others, but for me it's calculating everything correctly.

I guess to make certain that the Function creates a number you can change
Function N2MY(x) As Variant to
Function N2MY(x) As Single

 

[SkipVought]

@Zelgar

I also get a very small value

[tt]
=N2MY(A1-B1)
[/tt]
results in 0.0001

regardless if Variant or Single, small value!

 

[SkipVought]

I failed to reveal the results of my function

A1: 25.1727
B1: 24.088
[tt]
=D2MY(A1-B1)
[/tt]
Result: 1.0149

 

[mintjulep]

Convert to yards.
Do math.
Convert back to mm.yyyy.

Dist = mm.yyyy
DistInYards = 1760 * INT(Dist) + 1000 * (Dist - INT(Dist))

ConvertBack:

WholeMiles = INT(DistInYards/1760)
RemainderYards = MOD(DistInYards, 1760)
Some fiddling required for the padding zero.
"Add" them back together.

 

[NWildblood]

All, this has been extremely helpful and is much appreciated; I am now getting the results I wanted, can't thank you enough.

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

 

[SkipVought]

I am now getting the results I wanted..."

Using what?

 

[NWildblood]

@Skipvought; I spoke too soon, got some mixed results, will post how tomorrow. (Am not in the office today). Thanks again

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

 

[NWildblood]

OK, here are some results:

@SkipVought
A1 - B1 = C1
25.1727 - 24.0880 = 1.0847 EXPECTED RESULT

However, using A1-B1 I was getting 1.0149

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

In a second example,
A2 - B2
90.0836 - 89.1320

I expect to get 0.1276

However, using =D2MY1(A2-B2)
I am seeing 0.1675

And using =(D2MY(A2-B2)/1760)
I am seeing 0.9512

In a third example
A3 - B3
13.1337 - 13.0874

I expect to get 0.0463

However, using =D2MY1(A3-B3)
I am seeing 0.0081

And using =(D2MY(A3-B3)/1760)
I am seeing 0.0461

zelgar - despite numerous checks, I am now just getting #NAME?

I haven't got to trying to apply mintjulep's method yet, am off for some meetings now but will revisit this later today; thanks all again for your effort and time.

OOS




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

 

[PHV]

Provided that A1 and B1 are strings, you may create this 2 functions:

Public Function MY2Y(MY As String) As Long
Dim a
a = Split(MY, ".")
MY2Y = 1760 * a(0) + a(1)
End Function

Public Function Y2MY(Y As Long) As String
Y2MY = Y \ 1760 & "." & Format(Y Mod 1760, "0000")
End Function

and then in C1:
=Y2MY(MY2Y(A1)-MY2Y(B1))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NWildblood]

This works fine PHV. Thank you !

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