Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 16 Dec 2019 12:46:05 +0100SUM of columns not correcthttps://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/I have read various other posts and perhaps I am missing something, but I have been using this spreadsheet for quite some time and there has never been an error. Now the column total for Column I is not correct. It should be $2242.86, but is it coming up at $2242.73. Furthermore, when I add the 58.91 values together using the SUM function, it gives me 1531.53 instead of the correct value 1531.66. I believe I have searched for hidden values, etc, but I cannot resolve this. I have uploaded the file and appreciate your assistance! [C:\fakepath\TEST FILE.ods](/upfiles/15764476289665952.ods)
**[Edit - Opaque]** Inserted a space character after last exclamation mark to make the attached file a real link to a downloadable file.Sun, 15 Dec 2019 23:08:29 +0100https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/Answer by Opaque for <p>I have read various other posts and perhaps I am missing something, but I have been using this spreadsheet for quite some time and there has never been an error. Now the column total for Column I is not correct. It should be $2242.86, but is it coming up at $2242.73. Furthermore, when I add the 58.91 values together using the SUM function, it gives me 1531.53 instead of the correct value 1531.66. I believe I have searched for hidden values, etc, but I cannot resolve this. I have uploaded the file and appreciate your assistance! <a href="/upfiles/15764476289665952.ods">C:\fakepath\TEST FILE.ods</a></p>
<p><strong>[Edit - Opaque]</strong> Inserted a space character after last exclamation mark to make the attached file a real link to a downloadable file.</p>
https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/?answer=221562#post-id-221562Hello,
just format your cells in column `I` to numbers having 4 decimal places and you'll recognize the mismatch of your actual results and your expected ones. You seem to expect formatting also performs some kind of rounding of values to 2 digits, which isn't true. The sum in your sample file is absolutely correct, but sum calculates the numbers **stored (calculated)** in the cells and not those **shown** to you by telling LibreOffice just to show 2-decimal places.
Thus changing the formula to: `=ROUND((D3+E3)*0,7;2)` will solve your problem (using `SUM()` is superfluous in this case)
see also: [C:\fakepath\RoundingError-Modified.ods](/upfiles/15764495744843473.ods)
Hope that helps.
*If the answer is correct or helped you to solve your problem, please click the check mark (✔) next to the answer.*Sun, 15 Dec 2019 23:31:58 +0100https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/?answer=221562#post-id-221562Comment by thisuldoit for <p>Hello,</p>
<p>just format your cells in column <code>I</code> to numbers having 4 decimal places and you'll recognize the mismatch of your actual results and your expected ones. You seem to expect formatting also performs some kind of rounding of values to 2 digits, which isn't true. The sum in your sample file is absolutely correct, but sum calculates the numbers <strong>stored (calculated)</strong> in the cells and not those <strong>shown</strong> to you by telling LibreOffice just to show 2-decimal places.</p>
<p>Thus changing the formula to: <code>=ROUND((D3+E3)*0,7;2)</code> will solve your problem (using <code>SUM()</code> is superfluous in this case)
see also: <a href="/upfiles/15764495744843473.ods">C:\fakepath\RoundingError-Modified.ods</a></p>
<p>Hope that helps.</p>
<p><em>If the answer is correct or helped you to solve your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/?comment=221626#post-id-221626Thank so much!! I have been an Excel user for a very long time, and I am trying to make the switch to Calc. I really appreciate your assistance!!!!Mon, 16 Dec 2019 12:46:05 +0100https://ask.libreoffice.org/en/question/221559/sum-of-columns-not-correct/?comment=221626#post-id-221626