Excel mac 2008 bug: Some calculated values paste incorrectly into Prism.

The bug

The situation seems so simple. Copy results calculated in Excel Mac 2008, and paste into a data table in  Prism Mac 5.0c. What could be simpler? But there is a problem. In rare cases, a trailing zero is dropped.  If you copy 20, only the 2 may paste. This means the data pasted into Prism may differ substantially from the data created in Excel.

Details:

• This problem only occurs Prism Mac 5.0c and later.
• The bug is only within Excel 2008 (as far as we know). Windows versions of Excel don't have this bug.
• The problem does not occur with values you enter in Excel, but only with results calculated in Excel.
• It happens only with integers ending in zero. But not all such values. We've seen the problem only with 20, 120, 130, 150 and 240. 
• It happens only with results computed by certain Excel functions.  We've seen the problem only with the GEOMEAN() and EXP() functions, although it is possible others are affected as well.
• It happens only when you copy a block of data with at least two cells or an entire cell. If you copy only the text within one cell, the value pastes fine.
• It only happens when you use the Paste command, or use Paste Special and check the option in Prism's Paste Special dialog to paste as many digits as possible. This option is unchecked by default.

Why in 5.0c and not earlier?

When you copy a block of values in Excel, it puts those values on the clipboard in multiple formats. Earlier versions of Prism pasted the plain text rendition. This worked most of the time. But there were issues with commas vs. periods as decimal separators. And there was a problem that only the digits you saw on the Excel screen were pasted. If the cell was computed as 1/3, and formated to show 0.3, that is all that could be pasted into Prism. And there are problems with multiword column titles, especially if those titles include a comma.

Prism 5.0c, by default, uses the HTML (XML) rendition of the clipboard. This version includes more details, and includes the value in two formats. With one of these formats,  the results of the formula =1/3 appears as 0.3333333, even if only "0.3" shows in Excel. With this format, there is no ambiguity about column titles or decimal separators.  

How we know it is an Excel bug and not a Prism bug

We are 100% sure that the bug is within Excel,  and not within Prism. We can view  the XML version of the clipboard with a clipboard viewer, and see that Excel put the wrong values on the clipboard  (zeroes are dropped). We have reported the bug to Microsoft.

We don't understand how the bug occurred within Excel. It is very strange that only a few values produced by only a few functions lead to this bug.

Simple workaround

There are two simple workarounds. Either will work. Both involve dropping the Edit menu, choosing Paste Special and making choices on the first tab of that dialog. 

The first workaround is to check this option:

  [x] Paste the older text-based clipboard format

With that option checked, Prism 5.0c works like earlier versions.

The second option is to uncheck this option:

   [  ] Paste as many digits as possible. If Excel rounds to 1.23, paste 1.23456.

With this option unchecked, Prism pastes the same number of decimals as you see in Excel and it works fine. The XML copy of the clipboard has two versions of each number, and the version pasted when this option is not checked is fine.

While in Paste Special,  check the option to use that setting as the default for later pasting. 

How to reproduce the bug

If you want to see this bug in action, you can do so easily. Put this formula into a cell in Excel 2008 on a Mac:

    =EXP(LN(20)) 

The result, of course, is 20.

Copy that entire cell from Excel 2008.  Note that you must copy the entire cell, and not just the text within. Better, copy that cell plus an adjacent one, to be sure you are copying cells and not text. 

Go to Prism Mac 5.0c, drop the Edit menu and choose Paste special. Uncheck the first Excel option, and check the second one, on the first tab of that dialog:

     [ ] Paste the older text-based clipboard format 

    [x ]Paste as many digits as possible. If Excel rounds to 1.23, paste 1.23456

Click OK. The values 2 (not 20) is pasted.

 Another example: =GEOMEAN(20)

If you have a clipboard viewer, view the public.HTML version. Note that the num= area says "2" when it should be "20". It looks like this (using square rather than angle brackets so it will show ok here):

    [td height=13 align=right width=75 x:num="2"] 20 [/td]

How we have changed Prism 5.04

The Paste command in 5.0d works like the Paste Special command using the default default options. It will use the HTML version of the clipboard, but that version has the contents in two formats, and Prism 5.0d will Paste the version that has the values as shown in Prism without the extra digits ("0.3" rather than "0.33333333"). It is only the version that is supposed to show as many digits as possible that actually shows too few digits due to the Excel bug. 

We have reported the bug to Microsoft, and this bug is supposed to be fixed in Excel 2011.