0.05 (like most decimal fractions) is a non-terminating binary number.
Specifically, 0.05 is
1.100110011001100110011001100110011001100110011001 10011...B-5
where the notation means 2^-5 +2^-6 +2^-9 +... (analogous to decimal
notation). Double precison rounds this binary number to
1.100110011001100110011001100110011001100110011001 1010B-5
which has a decimal value of
0.050000000000000002775557561562891351059079170227 05078125
Single precision rounds it even futher to
1.10011001100110011001101B-5
which has a decimal value of
0.0500000007450580596923828125
as you can see by examining the value of y in
Dim x As Single, y As Double
x = 0.05
y = x

Since the single precision value is even farther from 0.05 than the
double precision value, the use of single precision just compounds the
problem.

When you use approximate inputs, it should be no surprise that the
output is also only approximate. Your choices are to either design your
programs to be tolerant of the approximations inherent in floating point
numbers, or else avoid them altogether. For instance, instead of
[a1] = 0
[a2].Formula = "=A1+0.05"
[a2].AutoFill [A2:A21]
you could use
For i = 1 To 21
Cells(i, 1) = (i - 1) / 20
Next

Jerry

Peter T wrote:

Hi,

The "Floating point" has been extensively discussed in this ng and I
understand why it causes errors. But I haven't found an "efficient" way to
deal with a simple thing like:
If cell-value = 1 Then 'blah

Sub test2()

[a1] = 0
[a2].Formula = "=A1+0.05"
[a2].AutoFill [A2:A21]
Debug.Print "[a21]=1", [a21] = 1 'False
Debug.Print 1 - [a21] '-2.22044604925031E-16

Dim nDouble As Double, nSingle As Single
nDouble = [a21]: nSingle = [a21]
Debug.Print "nDouble=1 ", nDouble = 1 'False
Debug.Print "nSingle=1 ", nSingle = 1 'True

'Single fixes above but causes problems later
nDouble = WorksheetFunction.Round(nDouble / 10, 2)
nSingle = WorksheetFunction.Round(nSingle / 10, 2)
Debug.Print " 'divide by 10 and round"
Debug.Print "nDouble"; nDouble, "nSingle"; nSingle
[c1] = nDouble
[c2] = nSingle
Debug.Print "[c1]=0.1", [c1] = 0.1
Debug.Print "[c2]=0.1", [c2] = 0.1, [c2] - 0.1
End Sub

Coercing to a Single appears to resolve my problem, but causes yet further
rounding problems downstream.

Of course could test with a Single then work with a Double. But this seems
like a lot of overhead in a long loop, and even more so in a UDF. Also could
use Round or in xl97 Worksheetfunction.round, but the latter in particular
is slow. Or maybe check within range +/- 1E-14.

Various alternative solutions have been suggested and Tom Ogilvy posted a
useful set of links he
http://tinyurl.com/662oo

However I would be grateful for advice as to the fastest / most efficient
way to ignore [say] the 14th decimal in a cell value.

TIA,
Peter T