3

I have an Excel spreadsheet (generated by spreadsheet gear, but it's more an Excel problem) that isn't functioning as i would expect when I use HLOOKUPS. I use a simple HLOOKUP to look up the value at a given percent in a table. For two certain values, the HLOOKUP fails. These values are 0.9993 and 0.999. This excel document demonstrates the problem: http://dsokol.com/tokio/excelna.xlsx. Cells C6 and C7 are showing as N/A, when they are intended to be populated with B and C. If I re-key over the values, the HLOOKUPs start working again.

Can anyone explain why this is occurring?

Justin Dearing
  • 14,270
  • 22
  • 88
  • 161
David J. Sokol
  • 3,456
  • 3
  • 31
  • 25

2 Answers2

5

The problem is those numbers are being stored as strings and not integers. I guess you need to explicitly declare the column type in SpreadSheet Gear.

If you open the xlsx file as a zip file, and extract Sheet1.xml from the xl\WorkSheets folder before and after overwriting the values, any diff tool (e.g. winmerge makes this obvious).

I have posted the XML differences on this gist.

Justin Dearing
  • 14,270
  • 22
  • 88
  • 161
  • I didn't know you could unzip excel files and view the internals. I was messing around with a hex editor for nothing! – David J. Sokol Oct 02 '12 at 18:19
  • @DavidJ.Sokol this is true for any MSFT file that has a four letter extention that ends in X. The X stands for XML. – Justin Dearing Oct 02 '12 at 18:23
  • 1
    In the Excel sheet posted all the values are numbers, e.g. =ISNUMBER(C2) and ISNUMBER(C5) both give TRUE. I suspect the cause is related to this http://support.microsoft.com/kb/214118 – barry houdini Oct 02 '12 at 18:28
2

I agree with Barry. This problem is not related to whether or not they are numbers or strings(they are all numbers). This appears to be a limitation of storing floating point numbers in binary - as described here: http://support.microsoft.com/kb/214118

Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation =1*(.5-.4-.1) may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.

This problem is not unique to excel either but rather a result of:

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

This phenomenon is visible here in "Arithmetic Equality":

InterestingProblem

Again this is not a problem that is unique to excel:

This behavior is not a problem in or a limitation of Excel or Works; this behavior occurs because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format.

Good Luck.

Stepan1010
  • 3,136
  • 1
  • 16
  • 21