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

Fireman_Simon

New Member
Joined
Nov 6, 2002
Messages
7
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try
Excel Formula:
=LEFT(A1,FIND(".",A1)-1)&"."&ROUNDUP(VALUE(MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1)-2))/22,0)
 
Upvote 0
Another option
Fluff.xlsm
AB
1
2113.0121113.06
3114.0198114.09
4116.0066116.03
Data
Cell Formulas
RangeFormula
B2:B4B2=ROUND(DOLLARFR(DOLLARDE(A2,1760),80),2)
 
Upvote 0
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.
 
Upvote 0
96.165 is 1650 yards not 165 yards, it needs to be 96.0165. Likewise 79.099 is 990 yards not 99 yards.
 
Upvote 0
try = INT(C20) & "m " & (ROUNDUP((MOD(C20,1)*100)/22,1)*10) & "chains"

assuming number is in cell C20
 
Upvote 0
That will give the wrong result for something like 116.0066 = 116m 66yds or 116.1 = 116m 1000yds
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top