Precision as Displayed flaw

[Will Fleenor]

Precision as Displayed flaw:
So why do we get the very poorly worded warning and why has this simple
problem not been fixed? Is it a difficult programming issue or has it just
not received adequate attention. Not needing =round() and not suffering
from unnoticed rounding errors in reports is after all a huge issue in the
minds of the vast majority of users (casual empiricysm after providing
training to 100000s of business professionals).

Even if this is a programming nitemare, the warning could at least be
revised to be useful. After all the only assumptions that are negatively
impacted are digits entered without the benefit of being proceed by the =
sign. Is there any other issue. I don't believe so. Formula are never
adversely impacted (if you don't believe this then provide an example). VBA
people are welcome to comment (I hope that is an acceptable way to address
this audience).

Even more importantly, the feature is never promoted by Microsoft and
virtually unknown to the vast majority of users who simply see calculations
that use undisplayed precision as errors that must be corrected by using the
=round() function. If someone would just tell them that there it is togel
switch that as been in the product for 20 years.

Will Fleenor

 

[Harlan Grove]

Precision as Displayed flaw:

Which is?

So why do we get the very poorly worded warning and why has this
simple problem not been fixed?  Is it a difficult programming issue
or has it just not received adequate attention.  Not needing
=round() and not suffering from unnoticed rounding errors in
reports is after all a huge issue in the minds of the vast majority
of users (casual empiricysm after providing training to 100000s of
business professionals).

...

Do you mean the online help entry (e.g., for Excel 2003)

'Precision as displayed Permanently changes stored values in cells
from full precision (15 digits) to whatever format, including decimal
places, is displayed.'

or the runtime dialog message

'Data will permanently lose accuracy.'

?

Fair criticism. Microsoft SHOULD expand on this. Indeed, precision
should be a top-level topic in online help contents. Not that many
more people would read it than currently read online help (fewer than
1 in 1,000, based on my own anecdotal empirical impression).

That said, while the majority of Excel users would presumably be
business users, Excel isn't designed as a business application. By
which I mean Excel uses hardware floating point arithmetic by default
and, as you point out below, for all intermediate formula calculations
and as results from all function calls. It's only at the final stage
of storing a cell's value that Excel imposes precision as displayed.

Why does Excel do this? Because fixed point (precision as displayed)
is only more *accurate* for addition and subtraction. [I'm assuming
readers understand the distinction between accuracy and precision.]
For multiplication, division, exponentiation and all other numerical
calculations, fixed point provides less accuracy than floating point.

So if your spreadsheets are just glorified accounting reports
involving just addition and subtraction, fixed point/precision as
displayed would be a net benefit. But if you need to handle fractional
multiplication, e.g., cash balances to the $0.01 and compound interest
rates with effective annual rates expressed to the basis point (0.01%)
or beyond, then PRECISE rounding rules become ESSENTIAL (e.g., tax
regulations in some countries or subnational governmental levels could
require rounding up at 0.5, 0.005, or truncating at whole numbers or
0.01, or bankers rounding). Precision as displayed only provides
standard rounding up at 1/2 finest precision.

Also, if you use *displayed* results of intermediate compound interest
calculations in subsequent compound interest calculations, precision
as displayed can be a huge PITA, e.g., amortization tables are
difficult to implement without using IPMT and PPMT, use of which means
using floating point.

Even more importantly, the feature is never promoted by Microsoft
and virtually unknown to the vast majority of users who simply see
calculations that use undisplayed precision as errors that must be
corrected by using the =round() function.  If someone would just
tell them that there it is togel switch that as been in the product
for 20 years.

The problem is that gains in addition/subtraction are offset by losses
in multiplication/division/exponentiation. Excel is used as a more
general purpose calculation tool than you suppose, and floating point
*IS* more accurate for *arbitrary* calculations than fixed point.
Precision as displayed may fix some problems, but it's likely to cause
just as many others if not more.

 

[Niek Otten]

<why has this simple problem not been fixed? Is it a difficult programming issue>

What exactly are you referring to?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Precision as Displayed flaw:
| So why do we get the very poorly worded warning and why has this simple
| problem not been fixed? Is it a difficult programming issue or has it just
| not received adequate attention. Not needing =round() and not suffering
| from unnoticed rounding errors in reports is after all a huge issue in the
| minds of the vast majority of users (casual empiricysm after providing
| training to 100000s of business professionals).
|
| Even if this is a programming nitemare, the warning could at least be
| revised to be useful. After all the only assumptions that are negatively
| impacted are digits entered without the benefit of being proceed by the =
| sign. Is there any other issue. I don't believe so. Formula are never
| adversely impacted (if you don't believe this then provide an example). VBA
| people are welcome to comment (I hope that is an acceptable way to address
| this audience).
|
| Even more importantly, the feature is never promoted by Microsoft and
| virtually unknown to the vast majority of users who simply see calculations
| that use undisplayed precision as errors that must be corrected by using the
| =round() function. If someone would just tell them that there it is togel
| switch that as been in the product for 20 years.
|
| Will Fleenor
|