Miles and Yards to Miles and Chains, but......... not allowed to use macros

[Fireman_Simon]

Hi all, I am attempting to convert a cell that contains miles and yards into miles and chains (railway).

Examples of cell data I have:
113.121 = 113m 121yds.
114.0198 = 114m 198yds
116.0066 = 116m 66yds

As you can see, they are messy and the digits after the decimal point isn't a mantissa in the normal sense.

So, to get the first example converted I would ignore the characteristic every time. I just need to treat the mantissa as whole numbers ie; ignore 113m. Then work on the mantissa of 121. => 121yds/22 = 5.5 chains
All I need is the answer in chains to be rounded to the nearest whole number, so I can use the ROUND function after I get the answer: 6 chains
Finally, I reinclude the characteristic and would have 113.6 ie 113m 6chains

I'm an excel smoothbrain folks. I would love it if you are able to help me. No macros allowed I'm afraid.

Thanking you in advance, Simon.

 

[snjpverma]

Try

=LEFT(A1,FIND(".",A1)-1)&"."&ROUNDUP(VALUE(MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1)-2))/22,0)

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1
2	113.0121	113.06
3	114.0198	114.09
4	116.0066	116.03
Data

Cell Formulas
Range	Formula
B2:B4	B2	=ROUND(DOLLARFR(DOLLARDE(A2,1760),80),2)

 

[Fireman_Simon]

Snjpverma and Fluff, that's done the trick, many thanks to the both of you.

 

[Fireman_Simon]

Ah, just found a slight issue folks.

So, prior to conversion, 96 miles and 165 yds is shown in my cell as 96.165
Converted by calculator to chains comes out as 69 miles 7.5 chains (165/22)

Snjpverma I get a return of 96.1

Fluff, the formula works in a lot of cases until I don't get a whole number as an answer. I got a return of 96.75 which is 96 miles and 7.5 chains, but....

Whilst the return is correct mathematically, to us simple railway folk it reads as 96 miles and 75 chains instead of 96 miles 7.5 chains . This means a walk of nearly one mile to the wrong location. Does that make sense?

What I'm trying to get is a return of 96.8 as I wish any mantissa to be rounded off to the nearest whole number. I think essentially, I'm trying to break the maths calculation result to read "incorrectly" mathematically, but correctly for what I need to see.

So, here's a couple distances and I'll put next to them what I would like the answer to show in the cell after the formula does it's stuff:

Original distance is miles and yards Calculation Round off the 7.5 to the nearest whole number Desired result in miles and whole chains

96.165 165/22=7.5 =8 96.8
79.099 99/22=4.5 =5 79.5

The even numbered returns in my cells all work wonderfully. Just need to fix the odds.

If this is word-salad, apologies. Please ask me to elaborate or PM me and I'll try and explain more.

Thank you.

 

[Fluff]

96.165 is 1650 yards not 165 yards, it needs to be 96.0165. Likewise 79.099 is 990 yards not 99 yards.

 

[I_know_nuffin]

try = INT(C20) & "m " & (ROUNDUP((MOD(C20,1)*100)/22,1)*10) & "chains"

assuming number is in cell C20

 

[Fluff]

That will give the wrong result for something like 116.0066 = 116m 66yds or 116.1 = 116m 1000yds

 

[I_know_nuffin]

96.165 is 1650 yards not 165 yards, it needs to be 96.0165. Likewise 79.099 is 990 yards not 99 yards.

as long as there is leading zeros (as per Fluff's post above) the below will work rounds up or down to nearest whole number of chains

= INT(C20) & "m " & ROUND(((MOD(C20,1)*10000) /22),0) & "chains"

 

[Fireman_Simon]

96.165 is 1650 yards not 165 yards, it needs to be 96.0165. Likewise 79.099 is 990 yards not 99 yards.

Agreed Fluff. Trouble is, that's not the data I get to work with. The decimal point in this case is purely to separate miles from yards. This is my problem.