Ask Your Question

Wrong number showing in simple calculation

asked 2018-04-06 12:21:25 +0100

SaintDave65 gravatar image

Hi there all, first question on here from me, image description 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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-04-06 12:27:56 +0100

Mike Kaganski gravatar image

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

edit flag offensive delete link more

answered 2018-04-06 12:31:54 +0100

Lupp gravatar image

updated 2018-04-06 12:33:50 +0100

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.

edit flag offensive delete link more

answered 2018-04-06 13:06:34 +0100

SaintDave65 gravatar image

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.image description 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.

edit flag offensive delete link more


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(<number>, "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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-06 13:43:21 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-06 13:44:09 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-06 12:21:25 +0100

Seen: 267 times

Last updated: Apr 06 '18