Dangerous rounding bug when placing/importingdata from Excel spreadsheet (CS2)

[Kal_Starkis@adobeforums.com]

We've just come out of a client meeting in which they pointed out a seemingly random spattering of data errors throughout the financial section of their annual report (which we have designed and typeset). We checked and confirmed that these errors were not present in the client's Excel file, and were therefore introduced by us -- an embarrassing situation, and a good way to lose a client if they were not so patient! Thankfully the errors were picked up before going to print.

The designer had imported the data from the Excel file using InDesign's place command, and it seems that in doing so InDesign has introduced the errors. These errors do not occur with a copy and paste directly out of Excel -- so I am assuming that InDesign is at fault, and not Excel.

Here's what's happening:

A seemingly (at first) random selection of numbers are out by 1 in InDesign -- and always 1 more, not less, than in the Excel file.

On closer inspection, all these numbers have something in common: the decimal amount (before rounding to whole numbers) is always close to but just below '.5'. For example, one of the numbers affected is '33,009.46'. Of course, Excel correctly rounds this down to '33,009'. InDesign incorrectly rounds it up to '33,010'.

We've tested this on two computers with the same results. We're using:
InDesign CS2
Excel X for Mac
Mac OS X 10.4.7

If anyone knows more about what's going on here than I do, by all means post to this thread. In any case I will put in a bug report with Adobe, and this post will hopefully serve as a warning to other designers working with Excel data in InDesign.

[Claudio_González@adobeforums.com]

I think I'm not quite following you. Does the original Excel file show already rounded numbers? In so, how is the rounding done?

In any case, I seem to recall solving similar problems by selecting the whole sheet, copying, and then pasting just the values. To a new file just in case.

[Kal_Starkis@adobeforums.com]

Does the original Excel file show already rounded numbers? In so, how
is the rounding done?




Yes, In Excel the cells are set to display figures rounded to the nearest dollar. It's only when I checked the actual numbers that had been entered by the client did I notice the pattern.

So I guess InDesign knows how the numbers are formatted in Excel and tries to match it when importing the data — and does a piss poor job of it as it turns out.

[Claudio_González@adobeforums.com]

I have just made some quick tests using ID 2 and Excel 2003, and can confirm the odd behaviour you report -plus some others that shouldn't affect you, like replacing the rest of the world's decimal separator (the comma) by the American one (the point). As a matter of fact, every number ending in .45 through .49 is wrongly rounded up in ID; and I cannot figure out where this curious rule comes from.

In any case, your problem has an easy solution. Open the Excel file before placing it, then go to Excel > Preferences > Calculations and check the Precision as Displayed box. Do a Save As (to keep the original file intact) and place the new file.

Of course, you'd be even safer if you asked your client to introduce this change.

[Claudio_González@adobeforums.com]

I forgot. It is most definitely a bug, and I find it curious that nobody had noticed before. At least, that I remember having read about it here.

[Claudio_González@adobeforums.com]

Out of curiosity, I started PageMaker 6.5.2 under Classic and repeated my test. Yes, you guessed correctly. I got the same wrong rounding when placing the same Excel file.

[Claudio_González@adobeforums.com]

Maybe this is not an Adobe bug after all, as I had been led to believe by my previous tests. Here are some further and puzzling findigs.

As I said in my last message, I placed in PageMaker 6.5.2 running in Classic the same Excel 2003 file I had used in my original tests, and got exactly the same wrong rounding.

For the sake of completeness, I have now started and old iBook with Mac OS 9.2.2, PageMaker 6.5.2 and Excel 2001, and copied the Excel 2003 test file to it.

When I opened PageMaker, I found I couldn't see the test file to place it. I thought, well Excel 2003 is too new for this machine, so I opened Excel 2001 and wrote exactly the same data in it. After saving it, I went to PageMaker and again couldn't find this new file when trying to place it.

I then opened the file in Excel 2001 and saved if in Excel 5 format. I could then see and place this new file in PageMaker. And now there was no wrong rounding up!!! All rounding was as it should be.

Hope somebody can make some sense out of all this.

[Claudio_González@adobeforums.com]

I was waiting for some feedback before performing further tests. It would seem I'm wasting my time.

[Sandee_Cohen@adobeforums.com]

I was waiting for some feedback before performing further tests. It would
seem I'm wasting my time.




Not at all. Any information such as this is well appreciated.

I also think it is important enough that you should post it to the Adobe bug report:

<http://www.adobe.com/misc/bugreport.html>

[Claudio_González@adobeforums.com]

Thanks, Sandee. However, I am no longer sure it is an Adobe bug and was wainting for someone to replicate my findings. It might be an OS X issue.

[Claudio_González@adobeforums.com]

It would be interesting to know if the problem also exists in Win machines.

[Bob Levine]

If you send me the files you're using, I'll be happy to check it out.

Click my name, go to my website for contact info.

Bob

[William_Byers@adobeforums.com]

I just tried a quick test with a small Excel file - no rounding error on the Windows machine. Used the same Excel file on the Mac and got the rounding error (1.45 rounded to 1, 1.46 through 1.49 rounded up to 2).

Both machines: IDCS2 (4.0.4)
Mac is running 10.3.9

[Claudio_González@adobeforums.com]

Bob, it doesn't matter what files you use. The OP used his client's, I made my own, Bill his own, and the wrong rounding is there.

[Claudio_González@adobeforums.com]

And apparently it is an OS X and not an Adobe bug, that also affects the decimal separator if you are using the non USA one.

[dkamien@adobeforums.com]

I have also run into a similar problem.

I create IDCS2 files both at home (MacOSX.4) and at work (MS Windows Pro), and occasionally have to import material into IDCS2 (Mac) from Excel files created in Excel for PC (opening the Excel files in Excel for Mac). In such cases, I get an error message, warning me that the data brought into the Mac by opening the PC file in Excel for Mac may not match the original data in the Excel for PC.

HTH.

Dave Kamien

[Claudio_González@adobeforums.com]

Hi Dave,

I think your problem is not similar to the rounding issue discussed here, but completely different. And from what you say, I would say it is an Excel problem, not an InDesign one.

If you want to receive help, I would suggest opening a new thread, and to give more details.

[Kal_Starkis@adobeforums.com]

I was waiting for some feedback before performing further tests. It would
seem I'm wasting my time.




Sorry Claudio for not responding earlier -- my subscription emails are getting wrongly identified as junk mail, and I only just thought to check back in.

Thanks for going to such a fantastic effort to try and isolate the problem and cause. I had already submitted a bug report to Adobe, but from what you're saying perhaps Microsoft or even Apple are at fault here. Or maybe it's some weird and mystical interaction between all three (InDesign, Excel, and Mac OS X) that is causing the problem. I'll submit a brief report of the issue to Apple.

[Kal_Starkis@adobeforums.com]

BTW...

... the rest of the world's decimal separator (the comma) by the American
one (the point).




In Australia we also use a period as the decimal separator, as do many other countries.

[Claudio_González@adobeforums.com]

Hello Kal,

I'm interested in knowing if my suggestion solved your rounding problem. I was under the impression you might receive similar jobs in the near future, and my suggestion would save you lengthy and boring checkings.

I was also under the wrong impression that the USA was the only country where the point is used as a decimal separator. As you are be better informed than me, could you please name those many others? Thanks.

BTW, the "americanization" affecting this part of the world has most people using the poin, which seems to be becoming a de facto norm. Of course this leads to confusions and funny situations, because this change does not include changing our thousands separator ... which is the point.