In Excel 2007:

I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions
treat .7 + .1 as something less than .8

Example:

Given the Range...

60% A
70% B
80% C
90% D

The formulas...
=VLOOKUP(.7,Range,2) returns B
=VLOOKUP(.8,Range,2) returns C
=VLOOKUP(.8+.1,Range,2) Returns D
as they all should.

However, the formula...
=VLOOKUP(.7+.1,Range,2) returns B.
or,
=VLOOKUP(.7+.1,Range,2,false) returns #N/A.

Certianly work arounds are possible, however it would seem that this is an
error on the part of Excel. Can anyone verify? How would one submit a bug
report to MS if it's even possible?

Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message ...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this is an
| error on the part of Excel. Can anyone verify? How would one submit a bug
| report to MS if it's even possible?
|
|
|
|

The point is that excel doesn't recognize that .7+.1 = .8 for the purposes
of the VLOOKUP function.


"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this is
an
| error on the part of Excel. Can anyone verify? How would one submit a
bug
| report to MS if it's even possible?
|
|
|
|

It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the two
cells together in D3 with =D1+D2, you get 0.8. Extending all three cells to
20 places of decimal shows trailing 0's after the 0.7, 0.1 and 0.8. Enter
0.8 in D4. Extend to 20 places of decimal and there are trailing 0's after
the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this is
an
| error on the part of Excel. Can anyone verify? How would one submit a
bug
| report to MS if it's even possible?
|
|
|
|

Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6, =0=D5
returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the
two cells together in D3 with =D1+D2, you get 0.8. Extending all three
cells to 20 places of decimal shows trailing 0's after the 0.7, 0.1 and
0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there are
trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this is
an
| error on the part of Excel. Can anyone verify? How would one submit a
bug
| report to MS if it's even possible?
|
|
|
|

Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the
two cells together in D3 with =D1+D2, you get 0.8. Extending all three
cells to 20 places of decimal shows trailing 0's after the 0.7, 0.1 and
0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there are
trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this
is an
| error on the part of Excel. Can anyone verify? How would one submit a
bug
| report to MS if it's even possible?
|
|
|
|

This is not a bug in Excel. Search the archives for "rounding issues" by
author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding error". If
you have Excel 2002 or later use formula auditing toolsevaluate formula and
you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the
two cells together in D3 with =D1+D2, you get 0.8. Extending all three
cells to 20 places of decimal shows trailing 0's after the 0.7, 0.1 and
0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there are
trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this
is an
| error on the part of Excel. Can anyone verify? How would one submit
a bug
| report to MS if it's even possible?
|
|
|
|

We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues" by
author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding error". If
you have Excel 2002 or later use formula auditing toolsevaluate formula
and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the
two cells together in D3 with =D1+D2, you get 0.8. Extending all three
cells to 20 places of decimal shows trailing 0's after the 0.7, 0.1 and
0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there are
trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C
=D3=D4 returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match
or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this
is an
| error on the part of Excel. Can anyone verify? How would one submit
a bug
| report to MS if it's even possible?
|
|
|
|

Search the archives for "rounding issues" by author Jerry Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues" by
author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding error".
If you have Excel 2002 or later use formula auditing toolsevaluate
formula and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX
MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add
the two cells together in D3 with =D1+D2, you get 0.8. Extending all
three cells to 20 places of decimal shows trailing 0's after the 0.7,
0.1 and 0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there
are trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match
or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that
this is an
| error on the part of Excel. Can anyone verify? How would one
submit a bug
| report to MS if it's even possible?
|
|
|
|

I am very familiar with floating point numbers. If your life depended on
TRUE or FALSE, if I choose A6, you live. A5 you die. They should both
produce either TRUE or FALSE. Two equal things, as A6 shows, should produce
0 when subtracted, as A5 does not. No way around that. There is either
equality or there is not.

Tyro

"T. Valko" wrote in message
...
Search the archives for "rounding issues" by author Jerry Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues" by
author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding error".
If you have Excel 2002 or later use formula auditing toolsevaluate
formula and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX
MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add
the two cells together in D3 with =D1+D2, you get 0.8. Extending all
three cells to 20 places of decimal shows trailing 0's after the 0.7,
0.1 and 0.8. Enter 0.8 in D4. Extend to 20 places of decimal and
there are trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C
=D3=D4 returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match
or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that
this is an
| error on the part of Excel. Can anyone verify? How would one
submit a bug
| report to MS if it's even possible?
|
|
|
|

I'll defer to the PhD expertise of Mr. Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
I am very familiar with floating point numbers. If your life depended on
TRUE or FALSE, if I choose A6, you live. A5 you die. They should both
produce either TRUE or FALSE. Two equal things, as A6 shows, should produce
0 when subtracted, as A5 does not. No way around that. There is either
equality or there is not.

Tyro

"T. Valko" wrote in message
...
Search the archives for "rounding issues" by author Jerry Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues"
by author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding error".
If you have Excel 2002 or later use formula auditing toolsevaluate
formula and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX
MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6,
=0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add
the two cells together in D3 with =D1+D2, you get 0.8. Extending all
three cells to 20 places of decimal shows trailing 0's after the
0.7, 0.1 and 0.8. Enter 0.8 in D4. Extend to 20 places of decimal
and there are trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C
=D3=D4 returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact
match or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that
this is an
| error on the part of Excel. Can anyone verify? How would one
submit a bug
| report to MS if it's even possible?
|
|
|
|

It is still an Excel issue. Excel is saying that two numbers, regardless of
rounding issues, when compared, are equal and when one is subtracted from
the other the result is not 0 - they are not equal. Excel is contradicting
itself. There may be hardware issues involved as to how the hardware does
comparisons as opposed to arithmetic. But, if so, Excel should take care of
that. How are we to trust Excel's tests for equality or subtraction, as the
example we have been looking at clearly shows? I wouldn't bet my life on
Excel!

Tyro

"T. Valko" wrote in message
...
I'll defer to the PhD expertise of Mr. Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
I am very familiar with floating point numbers. If your life depended on
TRUE or FALSE, if I choose A6, you live. A5 you die. They should both
produce either TRUE or FALSE. Two equal things, as A6 shows, should
produce 0 when subtracted, as A5 does not. No way around that. There is
either equality or there is not.

Tyro

"T. Valko" wrote in message
...
Search the archives for "rounding issues" by author Jerry Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both
ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues"
by author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding
error". If you have Excel 2002 or later use formula auditing
toolsevaluate formula and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX
MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In
D6, =0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add
the two cells together in D3 with =D1+D2, you get 0.8. Extending
all three cells to 20 places of decimal shows trailing 0's after
the 0.7, 0.1 and 0.8. Enter 0.8 in D4. Extend to 20 places of
decimal and there are trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C
=D3=D4 returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact
match or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that
this is an
| error on the part of Excel. Can anyone verify? How would one
submit a bug
| report to MS if it's even possible?
|
|
|
|

Sorry, misspoke. Not rounding issues, but representation issues.

Tyro
"Tyro" wrote in message
. net...
It is still an Excel issue. Excel is saying that two numbers, regardless
of rounding issues, when compared, are equal and when one is subtracted
from the other the result is not 0 - they are not equal. Excel is
contradicting itself. There may be hardware issues involved as to how the
hardware does comparisons as opposed to arithmetic. But, if so, Excel
should take care of that. How are we to trust Excel's tests for equality
or subtraction, as the example we have been looking at clearly shows? I
wouldn't bet my life on Excel!

Tyro

"T. Valko" wrote in message
...
I'll defer to the PhD expertise of Mr. Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
I am very familiar with floating point numbers. If your life depended on
TRUE or FALSE, if I choose A6, you live. A5 you die. They should both
produce either TRUE or FALSE. Two equal things, as A6 shows, should
produce 0 when subtracted, as A5 does not. No way around that. There is
either equality or there is not.

Tyro

"T. Valko" wrote in message
...
Search the archives for "rounding issues" by author Jerry Lewis.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
. net...
We know there is a discrepancy somewhere.

A1: 0.7
A2: 0.1
A3: =A1+A2 = 0.8
A4: = 0.8
A5: =A4-A3=0 = FALSE
A6: =A4=A3 = TRUE

Please explain that to me in great detail. You cannot have it both
ways.

Tyro


"T. Valko" wrote in message
...
This is not a bug in Excel. Search the archives for "rounding issues"
by author Jerry Lewis. He explains this in great detail.

As far as 0.7 + 0.1, try this:

A1 = 0.7
B1 = 0.1

=(A1+B1)-0.8=0

The result will be FALSE. This is due to a very small "rounding
error". If you have Excel 2002 or later use formula auditing
toolsevaluate formula and you'll see what is happening.



--
Biff
Microsoft Excel MVP


"C-L" wrote in message
...
Thanks for the validation Tyro.

I see the same behavior with respect .7+.1=.8=TRUE.

It seems as though the problem only manifests in VLOOKUP and INDEX
MATCH.




"Tyro" wrote in message
et...
Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In
D6, =0=D5 returned TRUE

Tyro

"Tyro" wrote in message
. net...
It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and
add the two cells together in D3 with =D1+D2, you get 0.8.
Extending all three cells to 20 places of decimal shows trailing
0's after the 0.7, 0.1 and 0.8. Enter 0.8 in D4. Extend to 20
places of decimal and there are trailing 0's after the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C
=D3=D4 returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact
match or approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH
functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that
this is an
| error on the part of Excel. Can anyone verify? How would one
submit a bug
| report to MS if it's even possible?
|
|
|
|

Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be
calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should be
2/3, but numerically the answer is correctly not equal to 0.6667 because of
the impact of finite precision.

In the same way for this calculation, A3 and A4 are correctly not equal to
each other. None of the numbers involved have exact binary representations,
and so the approximations to the original numbers result in the following
calculation:

0.699999999999999955591079014993738383054733276367 1875
+0.09999999999999997779553950749686919152736663818 359375
-------------------------------------------------------
0.799999999999999933386618522490607574582099914550 78125
which is not equal to
0.800000000000000044408920985006261616945266723632 8125

=(A4-A3) returns 1.11022302462516E-16 which is the correct 15-digit (Excel's
documented display limit) display of the correct difference of
1.1102230246251565404236316680908203125E-16 (which you can verify if you
care to do the math).

AFAIK, Excel's basic arithmetic is done in hardware, not software, and so
would give the same result produced by any other software package that does
not attempt to re-invent the wheel. Valid criticisms of Excel deal with
algorithm choice for calculations beyond basic arithmetic.

The inconsistent comparisons that you got are the result of MS muddying the
waters by vainly trying to "take care of that" as you requested. Since Excel
97, final subtractions of numbers that are equal to 15 decimal digits are
arbitrarily zeroed under the assumption that the very small difference is
unwanted residue of finite precision approximations. If such a subtraction
is not the final operation, then the actual difference is retained, since it
would reduce accuracy if the assumption were wrong. As a result, =(A4-A3)
and =A4-A3 do not return the same value, because surrounding parentheses mean
that the subtraction is not the final operation, and therefore the fuzz
factor is not applied. Similarly, =A4=A3 returns TRUE because the comparison
is the final operation, but =A4-A3=0 compares an unfuzzed (not final
subtraction) to 0 and correctly returns FALSE.

I consider this "optimization"
http://support.microsoft.com/kb/78113
to be a vain attempt, because no attempt to "take care of that" can succeed
in general. Whatever the level of fuzzing, it will be too much for some
calculations and not enough for others. Where it is too much, it will reduce
the accuracy of calculations. Instead, users need to be aware of the
limitations of finite precision arithmetic and do comparisons intelligently.
Instead of asking if A3=A4, ask if ABS(A3-A4)<epsilon, where epsilon is a
number that is appropriately small for the calculation (this has been taught
in programming courses for over half a century). An equivalent approach
would be to either round both numbers or the result to an appropriate level.
Given that all of the posted numbers have no more than one decimal place,
with only adding/subtracting, then rounding to one decimal place would be
perfectly reasonable.

Jerry

"Tyro" wrote:

It is still an Excel issue. Excel is saying that two numbers, regardless of
rounding issues, when compared, are equal and when one is subtracted from
the other the result is not 0 - they are not equal. Excel is contradicting
itself. There may be hardware issues involved as to how the hardware does
comparisons as opposed to arithmetic. But, if so, Excel should take care of
that. How are we to trust Excel's tests for equality or subtraction, as the
example we have been looking at clearly shows? I wouldn't bet my life on
Excel!

Tyro

You don't get it.

Excel has to produce the same results.
Two values, one in A1 and one in A2.
The formula =A1-A2=0 produces FALSE meaning that A1 is not equal to A2.
The formula =A1=A2 produces TRUE meaning that A1 is equal to A2.
The representation of numbers is meaningless. Excel is saying that FALSE is
equal to TRUE

Tyro


"Jerry W. Lewis" wrote in message
...
Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be
calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should
be
2/3, but numerically the answer is correctly not equal to 0.6667 because
of
the impact of finite precision.

In the same way for this calculation, A3 and A4 are correctly not equal to
each other. None of the numbers involved have exact binary
representations,
and so the approximations to the original numbers result in the following
calculation:

0.699999999999999955591079014993738383054733276367 1875
+0.09999999999999997779553950749686919152736663818 359375
-------------------------------------------------------
0.799999999999999933386618522490607574582099914550 78125
which is not equal to
0.800000000000000044408920985006261616945266723632 8125

=(A4-A3) returns 1.11022302462516E-16 which is the correct 15-digit
(Excel's
documented display limit) display of the correct difference of
1.1102230246251565404236316680908203125E-16 (which you can verify if you
care to do the math).

AFAIK, Excel's basic arithmetic is done in hardware, not software, and so
would give the same result produced by any other software package that
does
not attempt to re-invent the wheel. Valid criticisms of Excel deal with
algorithm choice for calculations beyond basic arithmetic.

The inconsistent comparisons that you got are the result of MS muddying
the
waters by vainly trying to "take care of that" as you requested. Since
Excel
97, final subtractions of numbers that are equal to 15 decimal digits are
arbitrarily zeroed under the assumption that the very small difference is
unwanted residue of finite precision approximations. If such a
subtraction
is not the final operation, then the actual difference is retained, since
it
would reduce accuracy if the assumption were wrong. As a result, =(A4-A3)
and =A4-A3 do not return the same value, because surrounding parentheses
mean
that the subtraction is not the final operation, and therefore the fuzz
factor is not applied. Similarly, =A4=A3 returns TRUE because the
comparison
is the final operation, but =A4-A3=0 compares an unfuzzed (not final
subtraction) to 0 and correctly returns FALSE.

I consider this "optimization"
http://support.microsoft.com/kb/78113
to be a vain attempt, because no attempt to "take care of that" can
succeed
in general. Whatever the level of fuzzing, it will be too much for some
calculations and not enough for others. Where it is too much, it will
reduce
the accuracy of calculations. Instead, users need to be aware of the
limitations of finite precision arithmetic and do comparisons
intelligently.
Instead of asking if A3=A4, ask if ABS(A3-A4)<epsilon, where epsilon is a
number that is appropriately small for the calculation (this has been
taught
in programming courses for over half a century). An equivalent approach
would be to either round both numbers or the result to an appropriate
level.
Given that all of the posted numbers have no more than one decimal place,
with only adding/subtracting, then rounding to one decimal place would be
perfectly reasonable.

Jerry

"Tyro" wrote:

It is still an Excel issue. Excel is saying that two numbers, regardless
of
rounding issues, when compared, are equal and when one is subtracted
from
the other the result is not 0 - they are not equal. Excel is
contradicting
itself. There may be hardware issues involved as to how the hardware does
comparisons as opposed to arithmetic. But, if so, Excel should take care
of
that. How are we to trust Excel's tests for equality or subtraction, as
the
example we have been looking at clearly shows? I wouldn't bet my life on
Excel!

Tyro

Numerically, the correct answer is FALSE in both instances, as I explained.

Presumably you want TRUE in both instances, and I explained the well known
approach you should take to get that numerically in finite precision. Taking
that approach would also avoid this inconsistency.

I agreed that attempting to smooth over the impact of finite precision
calculations was a bad idea ("vain attempt") that can produce inconsistent
results that are harder to explain than the mathematical inevitability that
they were attempting to "fix". However, since they haven't removed their
"optimization" after 4 subsequent Excel versions and 11 years of valid
criticism, including from some of the best names in floating point
arithmetic, such as
http://www.cs.berkeley.edu/~wkahan/Mindless.pdf
I doubt that MS will ever admit that it was a mistake.

The fact remains that Excel's basic arithmetic is identical to what you
would get from any other computer program that follows the IEEE 754 standard
for double precision (almost all software). The only way this unfortunate
"optimization" will hurt you is if you fail to take the precautions that you
should take for any floating point comparison in any software package.

Jerry

"Tyro" wrote:


You don't get it.

Excel has to produce the same results.
Two values, one in A1 and one in A2.
The formula =A1-A2=0 produces FALSE meaning that A1 is not equal to A2.
The formula =A1=A2 produces TRUE meaning that A1 is equal to A2.
The representation of numbers is meaningless. Excel is saying that FALSE is
equal to TRUE

Tyro


"Jerry W. Lewis" wrote in message
...
Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be
calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should
be
2/3, but numerically the answer is correctly not equal to 0.6667 because
of
the impact of finite precision.

In the same way for this calculation, A3 and A4 are correctly not equal to
each other. None of the numbers involved have exact binary
representations,
and so the approximations to the original numbers result in the following
calculation:

0.699999999999999955591079014993738383054733276367 1875
+0.09999999999999997779553950749686919152736663818 359375
-------------------------------------------------------
0.799999999999999933386618522490607574582099914550 78125
which is not equal to
0.800000000000000044408920985006261616945266723632 8125

=(A4-A3) returns 1.11022302462516E-16 which is the correct 15-digit
(Excel's
documented display limit) display of the correct difference of
1.1102230246251565404236316680908203125E-16 (which you can verify if you
care to do the math).

AFAIK, Excel's basic arithmetic is done in hardware, not software, and so
would give the same result produced by any other software package that
does
not attempt to re-invent the wheel. Valid criticisms of Excel deal with
algorithm choice for calculations beyond basic arithmetic.

The inconsistent comparisons that you got are the result of MS muddying
the
waters by vainly trying to "take care of that" as you requested. Since
Excel
97, final subtractions of numbers that are equal to 15 decimal digits are
arbitrarily zeroed under the assumption that the very small difference is
unwanted residue of finite precision approximations. If such a
subtraction
is not the final operation, then the actual difference is retained, since
it
would reduce accuracy if the assumption were wrong. As a result, =(A4-A3)
and =A4-A3 do not return the same value, because surrounding parentheses
mean
that the subtraction is not the final operation, and therefore the fuzz
factor is not applied. Similarly, =A4=A3 returns TRUE because the
comparison
is the final operation, but =A4-A3=0 compares an unfuzzed (not final
subtraction) to 0 and correctly returns FALSE.

I consider this "optimization"
http://support.microsoft.com/kb/78113
to be a vain attempt, because no attempt to "take care of that" can
succeed
in general. Whatever the level of fuzzing, it will be too much for some
calculations and not enough for others. Where it is too much, it will
reduce
the accuracy of calculations. Instead, users need to be aware of the
limitations of finite precision arithmetic and do comparisons
intelligently.
Instead of asking if A3=A4, ask if ABS(A3-A4)<epsilon, where epsilon is a
number that is appropriately small for the calculation (this has been
taught
in programming courses for over half a century). An equivalent approach
would be to either round both numbers or the result to an appropriate
level.
Given that all of the posted numbers have no more than one decimal place,
with only adding/subtracting, then rounding to one decimal place would be
perfectly reasonable.

Jerry

"Tyro" wrote:

It is still an Excel issue. Excel is saying that two numbers, regardless
of
rounding issues, when compared, are equal and when one is subtracted
from
the other the result is not 0 - they are not equal. Excel is
contradicting
itself. There may be hardware issues involved as to how the hardware does
comparisons as opposed to arithmetic. But, if so, Excel should take care
of
that. How are we to trust Excel's tests for equality or subtraction, as
the
example we have been looking at clearly shows? I wouldn't bet my life on
Excel!

Tyro

Formatting to show 20 decimal places accomplishes nothing, since Excel (as
documented) will never display more than 15 significant digits. If you ask
for more, Excel merely pads the dsiplay with meaningless zeros. To see what
is really going on, use my VBA function D2D from
http://groups.google.com/group/micro...fb95785d1eaff5

Most terminating decimal fractions are non-terminating binary fractions that
can only be approximated (just as 1/3 can only be approximated as a decimal
fraction).

The binary approximations to 0.7 + 0.1 produce
0.799999999999999933386618522490607574582099914550 78125
which is not equal to the binary approximation to 0.8
0.800000000000000044408920985006261616945266723632 8125

If you are only adding/subtracting 1-decimal-place numbers, then rounding
the result of calculations to 1 decimal place does no violence to the
calculation and avoids such surprises.

Jerry

"Tyro" wrote:

It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the two
cells together in D3 with =D1+D2, you get 0.8. Extending all three cells to
20 places of decimal shows trailing 0's after the 0.7, 0.1 and 0.8. Enter
0.8 in D4. Extend to 20 places of decimal and there are trailing 0's after
the 0.8.

=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4
returns TRUE

Something is not kosher.

Tyro

"Niek Otten" wrote in message
...
Use

=VLOOKUP(.7,Range,2,FALSE)

This is all explained in HELP; the 4th argument controls exact match or
approximation

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"C-L" wrote in message
...
| In Excel 2007:
|
| I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions
| treat .7 + .1 as something less than .8
|
| Example:
|
| Given the Range...
|
| 60% A
| 70% B
| 80% C
| 90% D
|
| The formulas...
| =VLOOKUP(.7,Range,2) returns B
| =VLOOKUP(.8,Range,2) returns C
| =VLOOKUP(.8+.1,Range,2) Returns D
| as they all should.
|
| However, the formula...
| =VLOOKUP(.7+.1,Range,2) returns B.
| or,
| =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
|
| Certianly work arounds are possible, however it would seem that this is
an
| error on the part of Excel. Can anyone verify? How would one submit a
bug
| report to MS if it's even possible?
|
|
|
|