Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    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.

  2. #2
    Platinum Lounger
    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((A1-INT(A1))-(B1-INT(B1))>0,1,-1)*MOD((A1-INT(A1))-(B1-INT(B1)),0.176)

    (subtracting cell B1 from A1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    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?!

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    <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>

  5. #5
    5 Star Lounger
    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •