Results 1 to 6 of 6
Thread: Miles and Yards (97)

20030113, 14:00 #1
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20030113, 14:12 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Miles and Yards (97)
I suspect this should do it:
=INT(A1)INT(B1)+IF((A1INT(A1))(B1INT(B1))>0,1,1)*MOD((A1INT(A1))(B1INT(B1)),0.176)
(subtracting cell B1 from A1)Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030113, 15:16 #3
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Miles and Yards (97)
Thank you. It's not quite working though.. there must be, perhaps, a sign wrong somewhere?!

20030113, 16:13 #4
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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((B1INT(B1))(A1INT(A1))>0,1,1)*MOD((B1INT(B1))(A1INT(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<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20030114, 09:48 #5
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20030114, 10:17 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Miles and Yards (97)
Assuming that B1 is never less than A1, you can use
=MAX(B1A1IF(B1INT(B1)<A1INT(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.