We will be migrating from Ask to Discourse on the first week of August, read the details here

# Is it possible to enable the "precision as shown" setting on a single sheet of a multiple sheet book.

I have enabled the above setting on one sheet of a multiple sheet book. Unfortunately this setting is now active on all other sheets, is there some way of preventing this ?.

edit retag close merge delete

AFAIK "no" - this settings changes the calculation algorithm.

( 2021-01-22 18:45:18 +0200 )edit
2

"Precision as shown" is a double edged sword. It is an easy way to eliminate accumulated rounding errors, typically where accounting calculations need to be "to the penny". As you have found, it affects all calculations in your spreadsheet file, so it will also mess up many calculations, typically those involving vastly different numbers (like multiplication/division where factors and answers are many orders of magnitude apart).

My advice: Avoid precision as shown except as a quick and dirty solution for a simple case. When you need to enforce a certain precision for a calculation, apply rounding in that formula using functions like ROUND() or MROUND().

( 2021-01-22 21:52:27 +0200 )edit

I would see the sword @keme mentioned one-edged with the sharp edge always pointing to your face.
If you need rounded values, always do the rounding explicitly by appropriate formulas which you can adapt to your actual needs (like by ROUND(), ROUNDUP(), ROUNDDOWN(), INT(), MROUND(), ...).
In addition to the very problematic scope of the discussed option, Formatting always rounds in its own ways. Would you know the effects concerning cells being formatted in a time style, as a fraction of the kind # ?/??, scientific, engineering?
In my case Q&D "just for the moment" might be forgotten when I start to work on the next inserted sheet. And enabling the option you haven't even the additional option to restrict this to the current document!

( 2021-01-24 14:15:13 +0200 )edit

Sort by » oldest newest most voted

The results need to be to the penny so the quick and dirty solution works perfectly for this sheet. I will move it out of it's present file. Thanks for your input.

more