When is equal not equal? Answer: In 2010

[natefarm]

I have an application that has been run by users every day in Excel 2003 for years without a problem. It loads data from two different sources (General Ledger and Teller transactions), compares the results for each teller, and highlights any differences. This is done for about 200 tellers spread out over about 20 branches.

One of the users that runs the process just switched to Excel 2010, and today it highlighted two of the 200 tellers, even though they weren't different. When someone else on 2003 ran it, it worked correctly. I can replicate the problem on 2010 vs. 2003 as well. I spent about an hour in the debugger comparing results and they look exactly the same, yet the If statement thinks they're different, as does comparing them in the Immediate window. But displaying the values in the Immediate window has them looking equal.

It's not a rounding or hidden decimal value issue, there are no hidden spaces or anything else I can find. I even tried multiplying the values by 1, but it still thinks they're different. I can make them the same by selecting the cell for the second value and hitting Enter. That would typically mean it's not a numeric value, but that is not the case, and it doesn't explain the 2003/2010 difference or why it works for all the tellers but these two.

Any suggestions would be appreciated (unless it's something I already tried, in which case I might say, "please read the above"

[Pete_UK]

I don't know why you think it is not a rounding issue, as that is what it seems to be to me, and I presume you are dealing with financial transactions. Just because a value might seem to be $123.45 for example, doesn't mean that it is stored as exactly that within Excel's memory, as some decimal fractions can't be stored exactly in a binary representation. Also, the calculation engines within Excel 2003 and Excel 2010 are not exactly the same, so they may treat rounded numbers differently.

Hope this helps.

Pete

[natefarm]

If that is true, how would I prove it? Where could I look to see the actual values it is comparing? Also, the numbers aren't calculated. They are pulled from a flat (.txt) file. No hidden decimal values there.