2

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.
phuclv
  • 26,555
  • 15
  • 113
  • 235
Patrick
  • 121
  • 2
  • http://floating-point-gui.de/errors/comparison/ – Mokubai Mar 01 '17 at 07:09
  • Hey Mokubai, the problem is not with my understanding of floating-point numbers. I've pretty extensively covered that topic through college classes and reading on my own. – Patrick Mar 01 '17 at 12:13
  • Fair enough, I didn't know your background and just thought it could be interesting reading. Posted before I saw the answer trail below. – Mokubai Mar 01 '17 at 12:15

1 Answers1

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.

Chindraba
  • 1,963
  • 1
  • 11
  • 25
  • Hey! Can you tell me how you were able to detect this character in A10? When I copy and paste it's contents into notepad and notepad++ I only see 471.4285714285710. Also, by selecting cell A10, pressing F2 and then ENTER corrects the problem; so does entering both cells A11 and A12 and doing the same (while leaving A10 untouched). – Patrick Mar 01 '17 at 01:35
  • Unfortunately it's just a trick of my eyes to notice minor variations in shapes. If you reload the sheet to what it was before, and then type the numbers in A10 and watch very carefully before entering or moving from the cell, you will notice a "twitch" in the size of row 10. – Chindraba Mar 01 '17 at 01:46
  • Hm, if you select the cell, press F2 then ENTER, nothing changes in the row size. I can't find any literature on the specific behavior of pasting as values, but clearly it's the culprit. If you copy and paste as values A2 to A10, the Boolean statements in B15 and C 15 become true, but copying and pasting as values A8 to A10 requires entering the formula bar for the cell and hitting enter for the Boolean statements to register correct. Yet copying and pasting both A2 and A8 into notepad++ (with display newline character enabled) gives the same number with newline. – Patrick Mar 01 '17 at 01:53
  • What are the chances that you have Notepad++ set to use Unix newlines? Could be a translation issue between Notepad++, Unix aware, and Excel, non-aware. The take-away is to use caution with copy/paste between apps. I guess? For what it's worth, the "error" reproduces in LibreOffice Calc in Linux as well, directly loading the downloaded file. – Chindraba Mar 01 '17 at 01:57
  • I will have to look into that setting with NP++. Definitely will be copy pasting with caution from now on, especially within Excel. I am really curious what goes on under the hood when copying and pasting as value the contents of a cell that has an equation (like A8). Thanks so much for looking into my issue, and leading me to a solution! I do have some more exploring to figure out the cause of that copy paste behavior. – Patrick Mar 01 '17 at 02:00
  • Wait, if you set A10:A12 equal to A8:C8, then the Boolean formulas in B16:C16 show false, lol. This is wonky. – Patrick Mar 01 '17 at 02:02
  • Reset the sheet to error condition. Set A10:A12 to A8:C8, then hand enter A3. – Chindraba Mar 01 '17 at 02:11
  • Hm, it appears that the sheet is not refreshing after opening the file and setting A10:A12 to A8:C8. Hand entering A3 as well as selecting any of A10:A12, hitting F2 to enter the formula editor and then hitting ENTER all refresh the sheet, and do the trick of fixing the Boolean errors. – Patrick Mar 01 '17 at 02:35
  • Well, have fun with it. Luck in your endeavors. – Chindraba Mar 01 '17 at 02:46