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]

OOP, I don't understand what your problem is.

149 yards is 149/1760 of a mile which is 0.084659091 miles. Hence .0847 mile.

?????

 

[SkipVought]

[highlight #FCE94F]Why are you dividing the difference by 1760???[/highlight]

 

[SkipVought]

In a second example,
A2 - B2
90.0836 - 89.1320

I expect to get 0.1276

0.1276, in your M.YYYY format means 1276 YARDS, which is 0.725 MILES

90.0836-89.1320 is 0.9516 MILES or .9516 * 1760 YARDS per MILE which is 1674.816 YARDS

D2MY(90.0836-89.1320) equals 0.1675: ZERO MILES and 1675 YARDS.

What is the issue???

 

[zelgar]

Since the numbers generated by the functions are NOT representative of the total number of miles, but a conglomerate of miles.yards, you cannot add or subtract them. The only way to add or subtract the numbers is to first convert them back to yards, then subtract and finally reconvert to the miles.yards format. The only reason why it worked on the first case was just dumb luck.

 

[strongm]

>The only reason why it worked on the first case was just dumb luck

Quite so

 

[SkipVought]

...and that is why the M.YYYY is represented as a STRING, rather than a decimal number. The representation as a decimal number could never be used in arithmetic, sine the integer part and the decimal part are DIFFERENT UNITS in this case.

That's why PHVs function MY2Y() works because it SPLITS the MILES & YARDS on the . delimiter and applies different arithmetic to each to return the equivalent YARDS.

 

[Andrzejek]

Maybe the best way to deal with all this mess is to convert everything to metric, do your math in metric, and convert it back to whatever you want...?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Why metric???

Like ANY mixed system (mi, yd, ft, in; hrs, min, sec; etc) pick any one of the units represented and convert everything to that unit.

 

[zelgar]

My problem is that I didn't understand that the OP already had the number in the Miles.Yards format and then wanted to subtract them. I thought he was wanting to first convert a number in yards to a Miles.Yards format and then subtract them later.

My original function would convert yards to the Miles.Yards format that's why I back calculated to figure out how he was getting the 25.1727 and 24.0880
A1 = N2MY(45727) = 25.1727
B1 = N2MY(43120) = 24.0880

I didn't realize he already had those numbers and wanted to subtract them, so the OP needed to go from Mile.Yards to yards, perform the subtraction and then revert to the Miles.Yards format.

 

[NWildblood]

Well - a great thanks to all of you, and apologies for taking up your time.

For clarification I currently work in the UK rail industry where data sets for assets going back a century in age are unfortunately recorded and reported in terms of miles and yards; perhaps I should have clarified this at the start. The industry has an asset register of >2m items for track assets alone this isn't set to change to metric anytime soon.

I hope the frustration for everyone who has had the good grace to pitch in some solutions will end here.

As per above, PHV's sollution is working fine.

Thank you all again, in appreciation.

 

[SkipVought]

No end! This has all been confusing and frustrating because you were not clear with your requirement.

It would have been helpful to state that your input cells (columns A & B) were expressed as M.YYYY. You seemed to imply that you simply wanted the RESULT to be expressed as M.YYYY and the inputs were MILES.

The raw difference (A1-B1) makes ABSOLUTELY NO MATHEMSTICAL SENSE, when columns A & B are expressed as M.YYYY!!! This is why you caused HUGE CONFUSION by including such arithmetic in your posts!!!

I suggest you think very carefully about how you state your problem, before posting additional threads.

 

[NWildblood]

@skipvaught "It would have been helpful to state that your input cells (columns A & B) were expressed as M.YYYY. You seemed to imply that you simply wanted the RESULT to be expressed as M.YYYY and the inputs were MILES. "


I had said
"Put simply, I need to determine the difference in Miles and Yards between e.g.
Mileage Work Order
MM.YYYY
25.1727

And

Mileage System
MM.YYYY
24.0880

Sorry if you found that misleading.

@PHB, thanks again for your solution, it works fine.

 

[zelgar]

Combining PHV's function together so you only need to do one in a Function(x,y) format...

Function OOS(x As String, y As String) As Variant
Dim a, b
a = Split(x, ".")
b = Split(y, ".")
xy = 1760 * a(0) + a(1)
yy = 1760 * b(0) + b(1)
Final = xy - yy
OOS = Final \ 1760 & "." & Format(Final Mod 1760, "0000")
End Function

 

[SkipVought]

So tell me why you were doing arithmetic on these M.YYYY values? Seems like a schoolboy blunder!

 

[NWildblood]

@Skipvaught, are you asking me what the object of this exercise is, or just launching a personal attack ?

 

[NWildblood]

Thanks @zelgar

 

[SkipVought]

Why were you using arithmetic on columns A & B, when they clearly cannot be subject to arithmetic?

 

[NWildblood]

Sheer incompetence Skip, as you have kindly established. Cheers.

 

[SkipVought]

A much clearer example might have been something like this
[tt]
1.0001 0.1759
[/tt]
You can do this difference in your head. 1 minus 9, Oh, I need to borrow from the miles, cuz I only have 1 yard. So zero miles and 1760+1 equals 1761, minus 1759 equals 2 yards. So the result is 0.0002. And of course PHVs function does that by converting EVERYTHING to a single unit before performing any arithmetic.

You can't do arithmetic directly on a mixed units value!

 

[zelgar]

Skip, it's the joy of dealing with legacy data and formatting. If you try to change it, it just messes up the everything. One day, they'll probably go metric; but unfortunately, OOS needs to use the format he's been stuck with.