Ask Your Question

When useing spread sheet how can I turn off rounding ? [closed]

asked 2013-12-15 22:35:34 +0200

William Gavin gravatar image

Answers alwas seem to come up with a + or a - 2 cents or so.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 17:22:01.721658

3 Answers

Sort by » oldest newest most voted

answered 2013-12-19 20:46:14 +0200

erAck gravatar image

For 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.

edit flag offensive delete link more

answered 2013-12-16 02:40:55 +0200

Rugslug gravatar image

Hi 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.


edit flag offensive delete link more


This solved my problem, thanks for posting !

ekoster gravatar imageekoster ( 2018-03-05 13:33:34 +0200 )edit

answered 2013-12-15 22:45:32 +0200

m.a.riosv gravatar image

Please @William, with a bit detailed explanation about what you want to do, maybe someone can help.

edit flag offensive delete link more


I'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..

William Gavin gravatar imageWilliam Gavin ( 2013-12-16 01:28:34 +0200 )edit

Question Tools

1 follower


Asked: 2013-12-15 22:35:34 +0200

Seen: 6,431 times

Last updated: Dec 19 '13