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

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

Jerry, that was quick! and thanks.

I should have explained the sample formula was only for demonstrating the
problem, and it's "no surprise" that it produces a rounding error.

My code runs on unknown data, which could be cell formulas or constants
(possibly pasted from formulas). I need to poll perhaps large numbers, the
vast majority of which are unlikely to present any sort of a problem.

The various methods I've tried to eliminate "false" results slow the process
at least to some extent, inevitably I suppose. My purpose and the point of
this post is to do exactly what you suggest:

"design your programs to be tolerant of the approximations inherent in
floating point numbers"

But in the fastest most efficient way possible.

Thanks and regards,
Peter

"Jerry W. Lewis" wrote in message
...
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

Don't know about the fastest, most efficient, but

Const dEPSILON As Double = 1E-10 'set appropriately
If Abs(Cells(21, 1).Value - 1) < dEPSILON Then ...


will work. Using Range references (Cells(21,1)) instead of the evaluate
method ([A21]) will likely speed up your code more than which method of
testing you use.



In article ,
"Peter T" <peter_t@discussions wrote:

The various methods I've tried to eliminate "false" results slow the process
at least to some extent, inevitably I suppose. My purpose and the point of
this post is to do exactly what you suggest:

"design your programs to be tolerant of the approximations inherent in
floating point numbers"

But in the fastest most efficient way possible.

I like using Currency since most comparisons like this are "usually" not
over 2 places to the right of the decimal point.

Sub Demo()
Dim n As Currency

[A1] = 0
[A2].Formula = "=A1+0.05"
[A2].AutoFill [A2:A21]

n = [A21]
Debug.Print "[A21]=1", n = 1 'Now True
Debug.Print 1 - [A21] '-2.22044604925031E-16
Debug.Print
End Sub

[a21] '-2.22044604925031E-16

For the Op, although cell A21 shows 1.000..., Excel works with only 15
digits. Your math coprocessor is using 17. Excel can not display your
coprocessor's internal number of 1.0000000000000002.
However, when you do the math subtraction, Excel is padding the hidden
ending 2 with zero's in base two. When converted to decimal, the displayed
numbers is not very meaningful.
Therefore, looking at you hidden '2'

? 2*2^-53
2.22044604925031E-16

--
Dana DeLouis
Win XP & Office 2003


"JE McGimpsey" wrote in message
...
Don't know about the fastest, most efficient, but

Const dEPSILON As Double = 1E-10 'set appropriately
If Abs(Cells(21, 1).Value - 1) < dEPSILON Then ...


will work. Using Range references (Cells(21,1)) instead of the evaluate
method ([A21]) will likely speed up your code more than which method of
testing you use.



In article ,
"Peter T" <peter_t@discussions wrote:

The various methods I've tried to eliminate "false" results slow the
process
at least to some extent, inevitably I suppose. My purpose and the point
of
this post is to do exactly what you suggest:

"design your programs to be tolerant of the approximations inherent in
floating point numbers"

But in the fastest most efficient way possible.

Hi JE,

Interesting and useful. I've just done a quick check of long loops of 4
methods, your Constant, coerce to a Single, Round, and a calculation with
Int().

Constant and the Single methods took virtually same time, Single method very
slightly faster. Both significantly faster than Round or Int.

But your Constant method has the distinct advantage of being much more
flexible, say checking to the nearest 5th decimal (actually with fewer
places the Constant method gets faster).

Re evaluate, yes - I never use square brackets in finished code.

Many thanks,
Peter

"JE McGimpsey" wrote in message
...
Don't know about the fastest, most efficient, but

Const dEPSILON As Double = 1E-10 'set appropriately
If Abs(Cells(21, 1).Value - 1) < dEPSILON Then ...


will work. Using Range references (Cells(21,1)) instead of the evaluate
method ([A21]) will likely speed up your code more than which method of
testing you use.



In article ,
"Peter T" <peter_t@discussions wrote:

The various methods I've tried to eliminate "false" results slow the
process
at least to some extent, inevitably I suppose. My purpose and the point
of
this post is to do exactly what you suggest:

"design your programs to be tolerant of the approximations inherent in
floating point numbers"

But in the fastest most efficient way possible.

Hi Dana,

I like using Currency since most comparisons like this are "usually" not
over 2 places to the right of the decimal point.

Indeed, in one routine I am only checking for <= 0 or =1

Just done a similar speed check as I mentioned in my reply to JE.
Currency vs Single vs Constant all about the same speed, the differences in
time are trivial.

Thanks for this and for your other comments.

Regards,
Peter

"Dana DeLouis" wrote in message
...
I like using Currency since most comparisons like this are "usually" not
over 2 places to the right of the decimal point.

Sub Demo()
Dim n As Currency

[A1] = 0
[A2].Formula = "=A1+0.05"
[A2].AutoFill [A2:A21]

n = [A21]
Debug.Print "[A21]=1", n = 1 'Now True
Debug.Print 1 - [A21] '-2.22044604925031E-16
Debug.Print
End Sub

[a21] '-2.22044604925031E-16

For the Op, although cell A21 shows 1.000..., Excel works with only 15
digits. Your math coprocessor is using 17. Excel can not display your
coprocessor's internal number of 1.0000000000000002.
However, when you do the math subtraction, Excel is padding the hidden
ending 2 with zero's in base two. When converted to decimal, the
displayed
numbers is not very meaningful.
Therefore, looking at you hidden '2'

? 2*2^-53
2.22044604925031E-16

--
Dana DeLouis
Win XP & Office 2003


"JE McGimpsey" wrote in message
...
Don't know about the fastest, most efficient, but

Const dEPSILON As Double = 1E-10 'set appropriately
If Abs(Cells(21, 1).Value - 1) < dEPSILON Then ...


will work. Using Range references (Cells(21,1)) instead of the evaluate
method ([A21]) will likely speed up your code more than which method of
testing you use.



In article ,
"Peter T" <peter_t@discussions wrote:

The various methods I've tried to eliminate "false" results slow the
process
at least to some extent, inevitably I suppose. My purpose and the
point
of
this post is to do exactly what you suggest:

"design your programs to be tolerant of the approximations inherent in
floating point numbers"

But in the fastest most efficient way possible.