5

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?

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
4
  • Here's what I found with my data. The calulated value is 0067.1000000000001000000000000000000, the hardcoded value is 0067.1000000000000000000000000000000, and ABS(E-F) comes out to 0000.0000000000000568434188608080000. Of course, this completely baffles me...how can math involving ONLY whole numbers and hundreths calculate a gazillionth? And why is the difference between the two not making sense? Are there inherent math issues in excel that I'm not aware of?
    – dwwilson66
    Commented Feb 14, 2013 at 16:24
  • both of your suggestions worked flawlessly, by the way... :)
    – dwwilson66
    Commented Feb 14, 2013 at 16:25
  • 3
    This is caused by the way Excel stores numbers and calculates with them - using "floating-point arithmetic" - see support.microsoft.com/kb/78113 - the normal method for working around is to use ROUND, using TEXT effectively achieves the same as rounding
    – barry houdini
    Commented Feb 14, 2013 at 16:28
  • Glad those options work for you! the formula you entered is not ONLY using the hundredths, it's using the full cell value, which is merely displayed to the hundredths place. Note that applying number formatting does not alter the value stored in the cell, it merely masks how that value is displayed to the user.
    – David Zemens
    Commented Feb 14, 2013 at 16:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .