excel formula not giving the correct answer

st999

I am trying to convert metres to feet and inches using excel.
If I convert 1.45 metres to feet and inches by multiplying 1.45*3.28 I get 4.76 and if I convert that to feet and inches using  =INT(F1)&"' "&TEXT(12*(F1-INT(F1)),"# #/#")&CHAR(34) I get 4' 9"
However the correct answer should be 4' 9 1/8" which you get if you use 4.76 instead of 1.45*3.28.

Why does multiplying 1.45 * 3.28 not give the same answer as using 4.76?

theoretica

Look to your rounding and number of significant figures carefully.  1.45*3.28 is not exactly 4.76 for a start.

4' 9 11/128 is closer

SalsaDanca

In the TEXT function, the formatting string "# #/#" will only display a fraction if the fraction has a 1-digit divisor (ie it will report 1/2 or 1/9, but not 1/10 or 1/12). You could try using "# #/12" which will force it to return a result to the nearest 12th of an inch, or "# #/#0" to return the correct result (in this case 9 1/14")

st999

Using  "# #/12" does give 4' 9 1/12" and  "# #/8" gives  4' 9 1/8" but how can I get 1.45 * 3.28 to equal the same 4.76 as 4.76 without altering the formula perhaps by formulating that cell in some way or is that not possible?
Even copying the cell which contains 1.45*3.28 to another cell and using that does not give 4' 9 1/8"
Or is this an excel bug?

st999

I've just tried this in LibreOffice and the formula works as expected though giving the answer as 4' 9 1/9" which is probably more correct.
Must be an excel bug

theoretica

1.45 x 3.28 = 4.756 so may not always give the same answer as running 4.76 through a formula.

debitcardmayhem

 little more correct is 4'9 +100000/114573" which is slightly less than 1/9th , Bored on a wet Saturday 1m~=3.28084 ft

st999

even multiplying 1.45 * 3.28084 still gives  4' 9"

debitcardmayhem

no it gives 4'9.086616"

WaywardDriver

First decide what precision you want. e.g. to nearest 1/8". Then if 1.45 in cell A1:
A2 =8*CONVERT(A1,"m","in") gives 456.69 (1/8th of inches)
A3 =INT(A2/96) gives 4 (feet)
A4 =INT((A2-A3*96)/8) gives 9 (inches)
A5 =INT(0.5+A2-A3*96-A4*8) gives 1 (nearest 1/8inch)
With some effort may be possible to combine into single formula.

Stompa

WaywardDriver said:

First decide what precision you want. e.g. to nearest 1/8". Then if 1.45 in cell A1:
A2 =8*CONVERT(A1,"m","in") gives 456.69 (1/8th of inches)
A3 =INT(A2/96) gives 4 (feet)
A4 =INT((A2-A3*96)/8) gives 9 (inches)
A5 =INT(0.5+A2-A3*96-A4*8) gives 1 (nearest 1/8inch)
With some effort may be possible to combine into single formula.

Though if you have 1.473 in A1 you get 4' 9 8/8". Whilst you could argue that that is correct, it's an odd way of writing it!