Crosstab Returns Zeros If Data in Scientific Notation

• Microsoft Excel for Windows, version 4.0

SYMPTOMS

In Microsoft Excel, when you use the Crosstab ReportWizard with a database that has a value category with very small values (for example, 1.00E-12), you will receive zero values (0) in the crosstab table.

CAUSE

The Crosstab ReportWizard attempts to round up the quantities in the value category beginning at the 9th decimal place (1.00E-9). Values smaller than this are assumed by the Crosstab ReportWizard to be zero.

WORKAROUND

To work around this problem, do the following:

1. In the field to be used as the value field, multiply all quantities by 1000:
   
   
   1. Enter 1000 in any blank cell.
   
   
   
   2. With the cell containing 1000 selected, choose Copy from the Edit menu.
   
   
   
   3. Highlight the cells containing the small values, and choose Paste Special from the Edit menu.
   
   
   
   4. In the Paste Special dialog box, under Operation, choose Multiply and then choose OK.
   
   
   



2. Use the Crosstab ReportWizard to create the crosstab table.



3. Do the following to divide the returned values by 1000:
   
   
   1. In any blank cell, type 1000.
   
   
   
   2. Select the cell that contains the value 1000, and choose Copy from the Edit menu.
   
   
   
   3. Select the cells containing the small values, and from the Edit menu, choose Paste Special.
   
   
   
   4. In the Paste Special dialog box, under Operation, choose Divide, and then choose OK.
   
   
   

Additional query words:

Keywords :
Version : 4.00
Platform : WINDOWS
Issue type :