Crosstab Returns Zeros If Data in Scientific Notation

Last reviewed: September 12, 1996
Article ID: Q119085

The information in this article applies to:

- 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:

In the field to be used as the value field, multiply all quantities by 1000:
a. Enter 1000 in any blank cell.
b. With the cell containing 1000 selected, choose Copy from the Edit
```
      menu.
```
c. Highlight the cells containing the small values, and choose Paste
```
      Special from the Edit menu.
```
d. In the Paste Special dialog box, under Operation, choose Multiply
```
      and then choose OK.
```
Use the Crosstab ReportWizard to create the crosstab table.
Do the following to divide the returned values by 1000:
a. In any blank cell, type "1000" (without the quotation marks).
b. Select the cell that contains the value 1000, and choose Copy from
```
      the Edit menu.
```
c. Select the cells containing the small values, and from the Edit
```
      menu, choose Paste Special.
```
d. In the Paste Special dialog box, under Operation, choose Divide,
```
      and then choose OK.
```

KBCategory: kbtool
KBSubcategory:
Additional reference words: 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.