Excel floating point and boolean unexplained behavior

Question

Thanks for checking out my question.

I was putzing around in Excel today for work, and encountered some anomalous behavior involving Excel's floating-point arithmetic and boolean tests. Here is a link to the Excel file test case I made.

In order to troubleshoot and gain more insight, I reviewed these pages: source 1, source 2 is Chip Pearson's post on Excel floating-point precision, and the third source is Wikipedia's article on precision in Excel (can't post more than 2 links due to reputation. Sorry, I'm new here).

The short of the problem is that cells A2:A4, A10:A12, I2:I4, and I10:I12 all have the exact same values in the exact same order, i.e., A2=I2=A10=I10, ..., A4 = I4 = A12 = I12. However, boolean tests to check whether arithmetic operations on the same values in different ranges do not yield the same results.

I thought it was due to floating-point precision, but the numbers in the ranges are the exact same. So any floating-point error should (I think) manifest itself identically across the ranges.

Finally, the cells are all formatted the same. So that should not be impacting the results.

If y'all have any idea what is causing this behavior, I would truly love to hear it. This problem has me dumbfounded!

I have outlined the exact steps I took to create the file below (to help with repeatability in case my Excel file is not clear).

My problem is as follows:

1. I calculated 6600/14, 4400/14, 2200/14, and copy-pasted the results as values into cells A2:A4.
2. I typed in formulas for boolean tests into cells A7:C7.
   2a. A7 had 'A2 = A3 + A4'
   2b. B7 had 'A3 = A2 - A4'
   2c. C7 had 'A4 = A2 - A3'
3. I typed text versions of the formulas (for readability) into cells A6:C6
4. I entered the following formulas in cells A8:C8
   4a. A8 had 'A3 + A4'
   4b. B8 had 'A2 - A4'
   4c. C8 had 'A2 - A3'
5. I copy-pasted as values and transposed the results from A8:C8 to A10:A12.
6. I then repeated steps 2-4 using the values in A10:A12.
7. Enter each cell A2:A4, A10:A12 and copy the value then paste it into notepad.
8. I pasted these values into cells I2:I4, I10:I12
9. Carry out the boolean tests from steps 2 - 4 for the ranges I2:I4 and I10:I12.

Answer 1

You're not going to like this! Somehow in the copy/paste process you grabbed a newline character and pasted it into cell A10 and that throws off all the math.