# Thread: Miles and Yards (97)

1. ## Miles and Yards (97)

Columns A and B have measurements such as, for example, 4.0125 and 4.0205 where the 4 indicates the number of miles and the decimal part represents a number of yards. I need to find the difference between the two (there are 1760 yards to a mile) but displaying the result in the same format. That is, 4.0075. I also need to do this as ONE formula! Any ideas?! Andy.

2. ## Re: Miles and Yards (97)

I suspect this should do it:

=INT(A1)-INT(B1)+IF((A1-INT(A1))-(B1-INT(B1))>0,1,-1)*MOD((A1-INT(A1))-(B1-INT(B1)),0.176)

(subtracting cell B1 from A1)

3. ## Re: Miles and Yards (97)

Thank you. It's not quite working though.. there must be, perhaps, a sign wrong somewhere?!

4. ## Re: Miles and Yards (97)

Jan Karel said that his formula subtracted B1 from A1; however, you said that you wanted to subtract A1 from B1, so his formula would be
<pre>=INT(B1)-INT(A1)+IF((B1-INT(B1))-(A1-INT(A1))>0,1,-1)*MOD((B1-INT(B1))-(A1-INT(A1)),0.176)</pre>

However, for your problem this gives the answer of 4.0205 - 4.0125 = 0.008. You said the answer was 4.0075. Are we missing something? --Sam

5. ## Re: Miles and Yards (97)

No, you're right. The answer should be 0.0080, i.e. 80 yards. There is an additional complication. If the two measurements are the same, so that the difference is zero, this should be displayed as 0.0001, i.e. 1 yard. (This relates to faults on railway lines.) Are we running out of the limit to the number of nested functions?

6. ## Re: Miles and Yards (97)

Assuming that B1 is never less than A1, you can use

=MAX(B1-A1-IF(B1-INT(B1)<A1-INT(A1),0.824,0),0.0001)

0.8240 = 1 - 0.1760; this value is subtracted if the decimal part of B is less than the decimal part of A.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•