When useing spread sheet how can I turn off rounding?

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

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.

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

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…

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.


This solved my problem, thanks for posting !