Excel Calc Error : 14 <> .14*100 ?!?!

I am using iserror(vlookup()) to determine if a value is in a table and have run across a weird calculation error in excel. It can be fixed using round() function but no other numbers seem to require the use of the function.

If try to find 14 in a table containg 14 using vlookup, there is no errror. If try to find a calculated 14 (via 100*.14), vlookup returns #VALUE error indicating value couldn't be found. No other values seem to recreate this error, and the error can be eliminated using the round() function, but curious as to if this is common?

More details on error scenarios/functional scenarios in spreadsheet.

APOLOGIES: first uploaded a values only version. current version has original formulas. my apologies.

Originally Posted by neon_e

I am using iserror(vlookup()) to determine if a value is in a table and have run across a weird calculation error in excel. It can be fixed using round() function but no other numbers seem to require the use of the function.

If try to find 14 in a table containg 14 using vlookup, there is no errror. If try to find a calculated 14 (via 100*.14), vlookup returns #VALUE error indicating value couldn't be found. No other values seem to recreate this error, and the error can be eliminated using the round() function, but curious as to if this is common?

More details on error scenarios/functional scenarios in spreadsheet.

APOLOGIES: first uploaded a values only version. current version has original formulas. my apologies.

Hi,

I find the area of error (rounding etc) to be around the 10 cent mark, ie, .1

As computers have no definitive value that is .1 (the binary value is .0011 repeating) the possibility of mis-match on numbers which are .1 .2 .3 etc is good. For .14 the binary appears to be .001000111101011100001010001111010111 which would give you a reasonable chance of not finding an exact value.

The Round() is certainly useful in these areas.

hth
---