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?
|
|
|
|

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?
|
|
|
|