Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 07 Jun 2018 12:11:39 +0200Calc sum function bug?https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/ If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )
Is this a bug, or am I not getting something here?Mon, 12 Feb 2018 09:17:10 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/Comment by Mike Kaganski for <p>If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )</p>
<p>Is this a bug, or am I not getting something here?</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=146070#post-id-146070Cannot reproduce, unless cells A1, A2, A4, A5, and A6 are text (in which case it's expected and normal).Mon, 12 Feb 2018 09:36:42 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=146070#post-id-146070Answer by mikalzet for <p>If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )</p>
<p>Is this a bug, or am I not getting something here?</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=146175#post-id-146175Thanks, that was it.Tue, 13 Feb 2018 10:53:45 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=146175#post-id-146175Comment by Jim K for <p>Thanks, that was it.</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=146201#post-id-146201This is not an answer and should be a comment instead. See [guidelines for asking](https://ask.libreoffice.org/en/question/137128/guidelines-for-asking#138301).Tue, 13 Feb 2018 16:10:33 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=146201#post-id-146201Answer by Cuadtigers for <p>If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )</p>
<p>Is this a bug, or am I not getting something here?</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=148484#post-id-148484The sum function does not work. I had to forward a document from one computer to the one with Excel for a summation. =sum(#:#) where # is your data, does not compute. Even if you hand enter all the numbers the old fashioned way with =#+#+#. Nada. But the exact same function in excel worked in seconds. Wed, 07 Mar 2018 16:51:24 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=148484#post-id-148484Comment by erAck for <p>The sum function does not work. I had to forward a document from one computer to the one with Excel for a summation. =sum(#:#) where # is your data, does not compute. Even if you hand enter all the numbers the old fashioned way with =#+#+#. Nada. But the exact same function in excel worked in seconds. </p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=148490#post-id-148490Because Excel does some stupid always-on automatism that delivers different results depending on the locale in which the spreadsheet is opened. In LibreOffice it depends on what setting is activated under Tools -> Options -> Calc -> Formula, Detailed Calculation Settings, Custom, Details, Conversion from text to number. The Excel equivalent is "Convert also locale dependent", but recommended is "Generate #VALUE! error" to spot such errors early.Wed, 07 Mar 2018 17:31:03 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=148490#post-id-148490Answer by erAck for <p>If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )</p>
<p>Is this a bug, or am I not getting something here?</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=148489#post-id-148489See also the [Text to Number FAQ](https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data).Wed, 07 Mar 2018 17:24:00 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=148489#post-id-148489Answer by Lupp for <p>If I open a new Calc spreadsheet, and I paste in the following numbers in a column ( cells A1 to A6):
0,0086584
0,00159885
0,0016177
0,00164682
0,00151831
0,00155874
In cell A7 I get the correct result if I write the formula = A1 +A2 + A3 + A4 +A5+ A6 ( 0,01659882 )
But if I press the sum icon, and have A7 formula as =SOMMA(A1:A6) I get a wrong answer ( 0,0016177 )</p>
<p>Is this a bug, or am I not getting something here?</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=146083#post-id-146083(Explaining the comment by @Mike Kaganski to more detail:)
The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts, and that the only true number in the range is in A3.
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error `#VALUE!`.
Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use `View > Value Highlighting Ctrl+F8` to check for my thesis.
A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right.
Down with silly automatisms! (There are a few clever ones, too.)Mon, 12 Feb 2018 10:59:05 +0100https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?answer=146083#post-id-146083Comment by BigMamaH for <p>(Explaining the comment by <a href="/en/users/5801/mike-kaganski/">@Mike Kaganski</a> to more detail:) </p>
<p>The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts, and that the only true number in the range is in A3. <br>
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error <code>#VALUE!</code>. </p>
<p>Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use <code>View > Value Highlighting Ctrl+F8</code> to check for my thesis.</p>
<p>A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right. </p>
<p>Down with silly automatisms! (There are a few clever ones, too.)</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=157140#post-id-157140Sorry, your explanation is wrong. I have the same problem. I changed the currency format to number format, removed the currency symbol, removed the space where the currency symbol was, saved, copied to a new sheet, and had the exact same problem.Wed, 06 Jun 2018 08:49:49 +0200https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=157140#post-id-157140Comment by Lupp for <p>(Explaining the comment by <a href="/en/users/5801/mike-kaganski/">@Mike Kaganski</a> to more detail:) </p>
<p>The only explanation not resorting to sorcery is that the somewhats contained in A1, A2, A4, A5, A6 and looking like numbers are actually texts, and that the only true number in the range is in A3. <br>
SUM() simply ignores text content while addition using the operator "+" automatically converts the operands to numbers if applicable, and otherwise returns an error <code>#VALUE!</code>. </p>
<p>Try {=SUM(VALUE(A1:A6))} (entered for array-evaluation) to confirm. Also use <code>View > Value Highlighting Ctrl+F8</code> to check for my thesis.</p>
<p>A valuable way to always clearly distinguish text from numbers is to NOT set an explicit alignment for the respective cells. The default alignment aligns texts left and numbers right. </p>
<p>Down with silly automatisms! (There are a few clever ones, too.)</p>
https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=157301#post-id-157301If you changed a cell's 'Numbers' format to an "ordinary" one (no currency, no time ..., "0.00" e.g. if your locale uses the decimal point), and the cell still displays a currency symbol, this is **evidence** for its containing **text**.
Did you already try the `View > Value Highlightging` (Ctrl+F8). Did you **de**-select any horizontal alignment? Did you ask the cell `=ISTEXT(myCellAddress)` entered into an empty cell?Thu, 07 Jun 2018 12:11:39 +0200https://ask.libreoffice.org/en/question/146068/calc-sum-function-bug/?comment=157301#post-id-157301