Excel – Why would two Excel cells that appear to have the same values test as not matching

microsoft excelworksheet-function

I'm creating a validation column for a worksheet. I've attached an image of what's going on.

  • Column A contains an indexing function and works properly. The formula appears at the bottom of the image.
  • Column B is hardcoded with the index values–no functions–cut-and-pasted from external File #2. This column will be used as a lookup column to bring additional data into the original document.
  • Column C compares the values in A/B and conditionally formats the cell. In the event that a line may be missing from one of the two files, we have a quick visual reference of where the lists fall out of sync so we may correct the error.
  • Column D is returning unexpected results, as you can see in the attached image.

Here are the things I have verified:

  • The worksheet has about 3000 lines and 40 columns. I've worked with much larger files before without incident.
  • Cols ABDF are formatted Number>Special>0000.00
  • Col CC, noted in the formula for AB, contains the number 2 or the number 3. I tried re-calculating after changing the number format from general to Number>Special>0000.00
  • Cols ABDF have been expanded to about 20 decimal places, and I did not spot a lonely .000000000001 in any of the values.
  • I've refreshed my data so everything re-calculates as current.
  • Cols D & F were created as tests just to see if could take the values and compare them and get different results. I did not get different results; my test appears on the uploaded image.
  • And yes, I did a cold reboot and started Excel all over again. 🙂

enter image description here

Any ideas or hints what's going on or where to look for how to correct it?

Best Answer

You could try comparing the values as text strings:

=IF(TEXT(E364,"0000.00")=TEXT(F364,"0000.00"),"OK","Danger Will Robinson!")

Alternatively, there may be a precision issue with the imported data from external file. IN which case, you can change your test:

=IF(Abs(E364-F364)<0.00001,"OK","Danger Will Robinson!")

The second option assumes that you can safely treat numbers that are 0.00001 difference as "equal". If not, just bump out the decimal places in that equation up to a max of I think 15 digits.

Related Question