excel formula not giving the correct answer

WaywardDriver

Stompa said:

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!

A4 =INT((A2-A3*96)/8+1/16) seems to work for your example but 1.523 gives 4' 12 0/8" so more refinement needed.

Stompa

WaywardDriver said:

Stompa said:

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!

Some refinement needed then!

I suspect
A2 =ROUND(8*CONVERT(A1,"m","in"),0)
A3 =INT(A2/96)
A4 =INT((A2-A3*96)/8)
A5 =INT(A2-A3*96-A4*8)
would be sufficient.

st999

debitcardmayhem said:

no it gives 4'9.086616"

It may give 4'9.086616" but it still shows (in excel) in the cell as  4'9" whereas if I open the saved spreadsheet in LibraOffice it shows as 4'9 1/9"
 I want to see 4'9 1/8" (or 4'9 1/9" if it is more accurate) in the cell in excel.
This is excel

4.76 4' 9"

4.76 4' 9"

5.81 5' 9 2/3"

3.28 3' 3 1/3"

3.28 3' 3 1/3"

3.80 3' 9 2/3"

3.80 3' 9 2/3"

3.80 3' 9 2/3"
and this is the same spreadsheet opened in LibreOffice

4.76 4' 9 1/9"

4.76 4' 9 1/9"

5.81 5' 9 2/3"

3.28 3' 3 3/8"

3.28 3' 3 3/8"

3.80 3' 9 2/3"

3.80 3' 9 2/3"

3.80 3' 9 2/3"

debitcardmayhem

Read here perhaps https://support.microsoft.com/en-us/office/display-numbers-as-fractions-0121ecac-1773-4f2d-8cd3-7db51fd83b77

st999

debitcardmayhem said:

Read here perhaps https://support.microsoft.com/en-us/office/display-numbers-as-fractions-0121ecac-1773-4f2d-8cd3-7db51fd83b77

It doesn't explain the discrepancy between excel and LibreOffice though.

WaywardDriver

st999 said:

debitcardmayhem said:

Read here perhaps https://support.microsoft.com/en-us/office/display-numbers-as-fractions-0121ecac-1773-4f2d-8cd3-7db51fd83b77

It doesn't explain the discrepancy between excel and LibreOffice though.

Seems spreadsheet precision bit of a minefield, see for example http://www.gnumeric.org/numerical-issues.html which indirectly contrasts Excel and LibreOffice calculation precision.  
Would be useful to know what OP is trying to achieve - academic or real-life exercise? if latter then convert to what precision? 

st999

WaywardDriver said:

Would be useful to know what OP is trying to achieve - academic or real-life exercise? if latter then convert to what precision? 

I just wondered why using excel if I put 4.76 in a cell and get 4' 9 1/8" yet if I did the same calculation using 1.45*3.28 which also shows in the cell as 4.76 I get 4' 9" and if there was a way to fix it?

 

WaywardDriver

st999 said:

WaywardDriver said:

Would be useful to know what OP is trying to achieve - academic or real-life exercise? if latter then convert to what precision? 

I just wondered why using excel if I put 4.76 in a cell and get 4' 9 1/8" yet if I did the same calculation using 1.45*3.28 which also shows in the cell as 4.76 I get 4' 9" and if there was a way to fix it?

 

The fundamental issue is to do with accuracy of the original figures i.e. 1.45 is to 3 significant figures so the answer should be also. Excel gives 1.45*3.28 as 4.756 which we should express as 4.76 to reflect the accuracy of the original measurement.
To look at this another way, by expressing the measurement as 1.45m we're implying it's between 1.445 and 1.455 which according to the Excel formula I used corresponds to between 4' 8 7/8" and 4' 9 2/8". This difference of 3/8" makes any difference in precision between how Excel and LibreOffice do calculations somewhat academic. 

DoaM

The fundamental issue is that 1.45 x 3.28 = 4.756 and not 4.76 ... 4.76 only show if you've set the cell formatting to 2 decimal places. The calculation still uses the 4.756 answer.

st999

If I add another column and roundup the answer to 1.45 x 3.28 = 4.756 and use that column I then get  4' 9 1/8" so basically it is a roundup error.

4.76	4.76			4' 9"	4' 9 1/8"
4.76	4.76			4' 9"	4' 9 1/8"
5.81	5.81			5' 9 2/3"	5' 9 5/7"
3.28	3.28			3' 3 1/3"	3' 3 1/3"
3.28	3.28			3' 3 1/3"	3' 3 1/3"
3.80	3.81			3' 9 2/3"	3' 9 5/7"
3.80	3.81			3' 9 2/3"	3' 9 5/7"
3.80	3.81			3' 9 2/3"	3' 9 5/7"