convert yards

[Les]

Does anyone know a fomula or function to convert yards to miles and yards
i.e. 2000 yards equals 1 mile and 240 yds not 1.14 miles.
Thanks

[Dav]

=TRUNC(A1/1760)&" Miles "&MOD(A1,1760)&" Yards"

or if you wanted it to be slightly more elaborate
=IF(A1<1760,A1& " yards",IF(A1<3520,TRUNC(A1/1760)&" Mile "&MOD(A1,1760)&" Yards",TRUNC(A1/1760)&" Miles "&MOD(A1,1760)&" Yards"))

Regards

Dav

[Les]

Cheers Dav
Absolutely spot on.
Les.


"Dav" wrote:

>
> =TRUNC(A1/1760)&" Miles "&MOD(A1,1760)&" Yards"
>
> or if you wanted it to be slightly more elaborate
> =IF(A1<1760,A1& " yards",IF(A1<3520,TRUNC(A1/1760)&" Mile
> "&MOD(A1,1760)&" Yards",TRUNC(A1/1760)&" Miles "&MOD(A1,1760)&"
> Yards"))
>
> Regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=563200
>
>