Removing Whole Numbers

[DIB]

Hi,

I'm trying to create a formula that will take into account the following:

I'm converting a site chainage (in metres) into miles and yards.

For example 268022 metres into 166 miles 950 yards - easily done into decimel [multiply 268022 by 1.0936, then divide by 1760 to give 166.539]. I then want to be able to calculate the decimel places (.539) into yards [by multiplying by 1760].

However my problem is how to remove the whole number (166) from the equation in order to be able to convert the decimel places?!

Any suggestions?

DIB

 

[barry houdini]

You could use this formula

=INT(A1/1609.3635)&" miles "&TEXT(MOD(A1*1.0936,1760),"0")&" yards"

where A1 contains the distance in metres

Note: excel has it's own CONVERT function available with Analysis ToolPak

you could use that in a similar manner, e.g.

=INT(CONVERT(A1,"m","mi"))&" miles "&TEXT(MOD(CONVERT(A1,"m","yd"),1760),"0")&" yards"

When I tested these given your example I got slightly different results

 

[DIB]

Superb! Thats perfect for my needs.

Many thanks!