Handle Floating point rounding errors, efficiently

  • Thread starter Thread starter Peter T
  • Start date Start date
P

Peter T

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 here:
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.10011001100110011001100110011001100110011001100110011...B-5
where the notation means 2^-5 +2^-6 +2^-9 +... (analogous to decimal
notation). Double precison rounds this binary number to
1.1001100110011001100110011001100110011001100110011010B-5
which has a decimal value of
0.05000000000000000277555756156289135105907917022705078125
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 said:
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 here:
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
Click to expand...
 
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 said:
0.05 (like most decimal fractions) is a non-terminating binary number.
Specifically, 0.05 is
1.10011001100110011001100110011001100110011001100110011...B-5
where the notation means 2^-5 +2^-6 +2^-9 +... (analogous to decimal
notation). Double precison rounds this binary number to
1.1001100110011001100110011001100110011001100110011010B-5
which has a decimal value of
0.05000000000000000277555756156289135105907917022705078125
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 said:
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 here:
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
Click to expand...
Click to expand...
 
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.
 
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
Click to expand...
Click to expand...

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 said:
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.



Peter T said:
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.
Click to expand...
Click to expand...
 
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 said:
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.



Peter T said:
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.
Click to expand...
Click to expand...
 
Hi Dana,
I like using Currency since most comparisons like this are "usually" not
over 2 places to the right of the decimal point.
Click to expand...

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 said:
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
Click to expand...
Click to expand...

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 said:
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.



Peter T said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top