Precision As Displayed (PAD) worked as you said it would and
I will keep in mind your recommendations.
Or non-recommendation ;-).
But to beat the dead horse one last time:
There are laws against that in some countries
.
My main point was not the inaccuracy of a specfic number
Nor was it mine. The point is: there are inaccuracies in almost
__all__ non-integers.
The effects are difficult to predict, especially the __cumulative__
effects of repetitive operations. Therefore....
So I should be able to use something like this
=IF(ROUND(MOD($A5,1),1)=1,0,MOD($A5,1))
Well, I wouldn't.
I would use one of the rounding expressions that I mentioned
previously, at least: =ROUND(A5+$B$2,1) to increment by 0.1 (B2)
expecting accuracy to 1 decimal place.
using Base = 5 and Increment = 0.1
errors occur for numbers 7.0, 8.0, 9.0, 10.0, 77.0 thru 299.0,
no errors for numbers 300.0 thru 508.0
Your perception that there are "no" errors with other numbers is
incorrect.
It is due, in part, to an Excel heuristic that attempts to "correct"
results that are "close to zero" [1].
Your perception is compounded by the fact that it appears that you
replaced MOD(A5,1) with A5-INT(A5). Nothing wrong with that; it just
exposes this Excel anomaly.
Let me explain....
If you compute 300 by adding 0.1 to 5 repetitively, the result is
actually 300-5*2^-44, slightly less than 300.
You can see this by computing =A2954-300-0 [1] and formatting as
Scientific. You will see that the difference is about -2.84E-13, a
very small number [4].
(Read: -2.84 times 10 to the -13 power. Also: -2.84 shift to the
right of the decimal point 13 times.)
And indeed, if you compute =MOD(A2954,1) or =A2954-INT(A2954)-0, the
result is about -2.84E-13.
Note: The negative result is reminiscent of a defect in the Excel INT
function [2].
In fact, MOD(A2954,1) and A2954-INT(A2954)-0 should return about
0.999999999999716. That is demonstrated by a VBA implementation of
MOD(x,1) [3].
But the important point is: the result of =MOD(A2954,1) and =A2954-
INT(A2954)-0 is not zero.
Summary....
As stated previously, most non-integer values cannot be represented
exactly in Excel (and most applications).
Arithmetic operations with these inaccurate non-integer values often
have inaccurate results.
It is difficult to predict, a priori, whether or not the results will
be inaccurate, and if so, in what way. In depends on many factors,
including but limited to the relative size of the operands of the
arithmetic operation.
To minimize computational anomalies when you expect/require results
accurate to a specific number of decimal places, it is important to
use some form of explicit rounding -- either the ROUND function
itself, or the "Precision As Displayed" calculation option.
But note: "Precision As Displayed" is __not__ recommended.
If you require more in-depth understanding, I can provide it. But it
would necessarily have to get into concepts like 53-bit mantissa, 80-
bit computation (FPU) and 64-bit floating-point storage.
If that sounds overwhelming -- and it is for most people -- just stick
with the mantra "explicitly round your computations prudently".
-----
Endnotes
[1] Re: A2954-300-0. The redundant "-0" is needed to avoid a self-
correcting heuristic in Excel, which tries recognize results that are
"close to zero" [sic] and replace them with zero. The dubious Excel
heuristic is poorly described under the misleading title "Example When
a Value Reaches Zero" in
http://support.microsoft.com/kb/78113.
[2] Mathematically, x-INT(x) cannot be negative. But Excel INT(x)
sometimes returns ROUNDUP(x,0) (!). I believe the root cause is the
same as the root cause of the defect poorly and incompletely described
in
http://support.microsoft.com/kb/161234.
[3] VBA UDF myMOD1:
Function myMOD1(x As Double) As Double
myMOD1 = x - Int(x)
End Function
[4] Instead of computing 300 by adding 0.1 to 5 repetitively, try
computing 300 by =5+(3000-50)*0.1. You will see that that computes
300 exactly, by coincidence. That should give you some idea of the
difficulty in predicting results of floating-point operations.
