Wrong number showing in simple calculation

[SaintDave65]

Hi there all,
first question on here from me,

I am trying to get this spreadsheet to work out loss/gain for weight. All is fine apart from the F column which for some reason keeps producing numbers like the one shown. As can be seen, unless the reference cell is blank, then it is simply initial weight - new weight and then add “kgs” to the end. But as shown in the example, 101.6 - 97=4.5999etc instead of simply 4.6.

I have highlighted the cell and even the column and gone to format and tried to change it so that it shows to 1 or 2 dp but this just does not seem to want to change. Am I missing something obvious here folks?

Thanks in advance

[mikekaganski]

See this Wikipedia article. The result is normal, and should be expected when working with limited-precision representation of floating-point values.

[Lupp]

Use the ROUND() function. Small differences of the kind you found are often due to unavoidable peculiarities of machine arithmetic which is not based on decimal representations. To understand the isuue fully you need to go into details of mathematics and of computer arithmetic. You can start with this recent thread in another forum.

[SaintDave65]

Thanks for your answers.
I have kind of fixed it now by simply removing the &kg bit from the formula. It then reports it ‘properly’ and allows me to format to 2dp etc.

As the column has kgs mentioned, that is fine by me and of course allows me to resize the column width.

Thanks for your help - much appreciated.

[mikekaganski]

Ah, so the problem was that display was “wrong” (too precise and verbose) when you used the string concatenation involving automatic conversion of numbers to strings, and that was just to show the units?

Well - this has several solutions. One of them is to use ROUND which was mentioned by @Lupp. The other is using TEXT(, “0.00 lbs”) to explicitly format the string.

But both of them are conceptually wrong. You need to use Numeric cell format, and use the “0.00 lbs” as format string.

[mikekaganski]

That would allow you to both display the value as you need it, and keep it numerical to allow it, e.g., to be used in further calculations.