Exported Crystal Report Shows -0 in Excel 1

[dianemarie]

I export reports to Excel from a report writer (Crystal). One of the columns of the report is a calculation and the answer is often "0". If I print the report from Crystal, all is fine. However, when it exports, SOME (not all) of the records show -0! I didn't even know you could get Excel to display a -0. I certainly can't unless the cell is formatted as text. However, the columns that display the -0 in Excel are formatted as Currency. Any thoughts? I believe the problem is being generated from Excel, not the report-writer. I would like to know if anyone can get Excel to display a -0, that might help me figure this one out. Thank you.

 

[DaleWatson123321]

Hi dianemarie,

Having noticed that no one has yet responded, I thought I should contribute the following:

In the past, I have had similar situations which were caused by "trailing numbers" (after the decimal) which are caused when one number is divided by another number.

In your data imported from Cystal, is it possible that the numbers contain such "trailing decimals" - which show up as "-0" because the width of the cell is too narrow to display the entire number.

To view an example of what I am describing, try this in Excel:

1) Made the width of column "A" a width of 5

2) In cell "A1", enter "-1".

3) In cell "A2", enter "=A1/1000".

Your result should be "-0".

If you then modify your formula in "A2", to include the ROUND function ...i.e. =ROUND(A1/1000,0) ...your result should be "0" - without the "-".

You are probably already aware, but just in case, in Excel, you are able to format an entire sheet with formatting which will cause cells with "0" to display "blank". It is under Tools, Options, View ...Zero Values. Remove the check-mark opposite "Zero Values".

Once you have confirmed the formatting situation, the next step (that I have been leading up to), is to use the ROUND function in Crystal Reports. Hopefully, it will result in the data comming into Excel without any "trailing decimals", and will eliminate your problem.

Hope this helps.

Let me know how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dianemarie]

Thank you Dale! I do not write the Crystal reports but I will be passing your information on to our analyst. For my purposes, I can instruct the users to display the 0's as blanks for printing purposes. We were comfortable that the data was correct, but did not want to send out reports with -0's to our clients, and the show 0's as blanks is a good quick way to do it.