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

Ask Your Question

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

asked 2021-01-22 18:39:01 +0200

ginnes gravatar image

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 flag offensive close merge delete


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

Opaque gravatar imageOpaque ( 2021-01-22 18:45:18 +0200 )edit

"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().

keme gravatar imagekeme ( 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!

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

1 Answer

Sort by » oldest newest most voted

answered 2021-01-24 13:58:41 +0200

ginnes gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-01-22 18:39:01 +0200

Seen: 64 times

Last updated: Jan 24