Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 06 Apr 2018 13:44:09 +0200Wrong number showing in simple calculationhttps://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/Hi there all,
first question on here from me, ![image description](/upfiles/1523009661868106.jpg)
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 Fri, 06 Apr 2018 12:21:25 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/Answer by Mike Kaganski for <p>Hi there all,
first question on here from me, <img alt="image description" src="/upfiles/1523009661868106.jpg">
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.</p>
<p>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?</p>
<p>Thanks in advance </p>
https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151553#post-id-151553See [this Wikipedia article](https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems). The result is normal, and should be expected when working with limited-precision representation of floating-point values.Fri, 06 Apr 2018 12:27:56 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151553#post-id-151553Answer by Lupp for <p>Hi there all,
first question on here from me, <img alt="image description" src="/upfiles/1523009661868106.jpg">
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.</p>
<p>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?</p>
<p>Thanks in advance </p>
https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151554#post-id-151554Use 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](https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=93042) in another forum.Fri, 06 Apr 2018 12:31:54 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151554#post-id-151554Answer by SaintDave65 for <p>Hi there all,
first question on here from me, <img alt="image description" src="/upfiles/1523009661868106.jpg">
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.</p>
<p>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?</p>
<p>Thanks in advance </p>
https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151559#post-id-151559Thanks 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](/upfiles/15230127186849341.jpg)
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.Fri, 06 Apr 2018 13:06:34 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?answer=151559#post-id-151559Comment by Mike Kaganski for <p>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.<img alt="image description" src="/upfiles/15230127186849341.jpg">
As the column has kgs mentioned, that is fine by me and of course allows me to resize the column width. </p>
<p>Thanks for your help - much appreciated.</p>
https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?comment=151563#post-id-151563Ah, 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.Fri, 06 Apr 2018 13:43:21 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?comment=151563#post-id-151563Comment by Mike Kaganski for <p>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.<img alt="image description" src="/upfiles/15230127186849341.jpg">
As the column has kgs mentioned, that is fine by me and of course allows me to resize the column width. </p>
<p>Thanks for your help - much appreciated.</p>
https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?comment=151564#post-id-151564That 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.Fri, 06 Apr 2018 13:44:09 +0200https://ask.libreoffice.org/en/question/151551/wrong-number-showing-in-simple-calculation/?comment=151564#post-id-151564