Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 05 Mar 2018 13:33:34 +0100When useing spread sheet how can I turn off rounding ?https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/Answers alwas seem to come up with a + or a - 2 cents or so.Sun, 15 Dec 2013 22:35:34 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/Answer by Rugslug for <p>Answers alwas seem to come up with a + or a - 2 cents or so.</p>
https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27173#post-id-27173Hi William,
The spreadsheet calculations contain true figures, however, what it displays depends on what you want to display.
Example: 2/3 =0.666666666... If you press the currency format it may display 0.67 (but it really contains 0.666666666...
If you press the "Add a Decimal" button, you will see the 6 count increasing, with the right most number still a 7. So it has not rounded it off, just displaying it as rounded off.
Likely for your financial calculations, you will want to round off each calculation (as opposed to mathematics where you would round off the final answer).
There are at least three functions that will help you.
ROUNDOFF(Cell,#) -- this will truncate to the # of decimals specified applying roundoff rules.
ROUNDUP(Cell,#) -- round up and truncate to # of decimals. So 0.3333 to 2 decimals rounds to 0.34
ROUNDDOWN(Cell,#) -- round down and truncate to # of decimals. So 0.6666 to 2 decimals rounds to 0.66, but if you press the add a decimal it becomes 0.66000000000.
"Cell" is a reference cell or value.
Tim
Mon, 16 Dec 2013 02:40:55 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27173#post-id-27173Comment by ekoster for <p>Hi William,</p>
<p>The spreadsheet calculations contain true figures, however, what it displays depends on what you want to display.</p>
<p>Example: 2/3 =0.666666666... If you press the currency format it may display 0.67 (but it really contains 0.666666666...</p>
<p>If you press the "Add a Decimal" button, you will see the 6 count increasing, with the right most number still a 7. So it has not rounded it off, just displaying it as rounded off.</p>
<p>Likely for your financial calculations, you will want to round off each calculation (as opposed to mathematics where you would round off the final answer).</p>
<p>There are at least three functions that will help you.</p>
<p>ROUNDOFF(Cell,#) -- this will truncate to the # of decimals specified applying roundoff rules.
ROUNDUP(Cell,#) -- round up and truncate to # of decimals. So 0.3333 to 2 decimals rounds to 0.34
ROUNDDOWN(Cell,#) -- round down and truncate to # of decimals. So 0.6666 to 2 decimals rounds to 0.66, but if you press the add a decimal it becomes 0.66000000000.</p>
<p>"Cell" is a reference cell or value.</p>
<p>Tim</p>
https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?comment=148270#post-id-148270This solved my problem, thanks for posting !Mon, 05 Mar 2018 13:33:34 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?comment=148270#post-id-148270Answer by erAck for <p>Answers alwas seem to come up with a + or a - 2 cents or so.</p>
https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27322#post-id-27322For exact financial calculation rounded properly to 2 decimals you always have to do the rounding yourself by using ROUND(...,2) function calls at appropriate places.
Either that, or you switch on the Tools->Options->Calc->Calculate "Precision as shown" option and format currency cells to 2 decimals. Which doesn't round intermediate results within a formula expression though, only the formula cell results that are passed on to other calculations as reference.
Effectively it's up to you and how complicated your calculations are and where they need rounding.
Thu, 19 Dec 2013 20:46:14 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27322#post-id-27322Answer by m.a.riosv for <p>Answers alwas seem to come up with a + or a - 2 cents or so.</p>
https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27168#post-id-27168Please @William, with a bit detailed explanation about what you want to do, maybe someone can help.Sun, 15 Dec 2013 22:45:32 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?answer=27168#post-id-27168Comment by William Gavin for <p>Please @William, with a bit detailed explanation about what you want to do, maybe someone can help.</p>
https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?comment=27172#post-id-27172I'm useing the spreadsheat to figure payrools, weekly; small business and totals in some columns give an incorrect answer because it will round up or round down a few cents. I need a true figure. The totals I check on the caculator give the true answer..Mon, 16 Dec 2013 01:28:34 +0100https://ask.libreoffice.org/en/question/27167/when-useing-spread-sheet-how-can-i-turn-off-rounding/?comment=27172#post-id-27172