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.