XL: Bad Zero Value When You Fill Linear Trend Series

ID: Q175752

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you fill a linear trend series in a worksheet in the versions of Microsoft Excel listed at the beginning of this article, the series may contain a value similar to either of the following:

2.84217E-14

-1.70530E-13

For example, if you expect the linear trend series to appear as follows

100 75 50 25 0 -25 -50

it appears as follows:

100 75 50 25 2.84217E-14 -25 -50

CAUSE

This problem may occur if the following conditions are true:

The linear trend series should contain the value zero (0).

You are using the right mouse button (Microsoft Excel for Windows or Microsoft Excel for Windows NT), or you are pressing the CONTROL key and clicking the mouse button (Microsoft Excel for the Macintosh) to create the linear trend series.

Specifically, this problem occurs because of a rounding error that occurs in Microsoft Excel when you work with near-zero values. For more information about the rounding errors, please see the following article in the Microsoft Knowledge Base:

Q78113 XL: Floating-Point Arithmetic May Give Inaccurate Results

WORKAROUND

To work around this problem, clear the cell that contains the incorrect value, and enter a zero (0) in the cell.

To prevent this problem from occurring, fill the linear trend series by selecting the cells to be used by the series, clicking the Edit menu, pointing to Fill, and then clicking Series. The problem does not occur when you use this method to fill a linear trend series.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Example

In Microsoft Excel, you can create a linear trend series in which each value is separated from the next value in the series by a set amount. To do this and see an example of the problem, follow these steps:

Enter the values that you want to use to establish the series. For example:
A1: 100
A2: 75

Select the cells. In Microsoft Excel for Windows or Microsoft Excel for Windows NT, right-click the fill handle at the lower-right corner of cell A2. In Microsoft Excel for the Macintosh, hold down the CONTROL key and click the fill handle at the lower-right corner of cell A2. Drag the fill handle down to cell A9. Then, release the mouse button.

On the shortcut menu, click Linear Trend.

You should see the following values:

A1: 100
A2: 75
A3: 50
A4: 25
A5: 2.84217E-14
A6: -25
A7: -50
A8: -75
A9: -100

Each value in the series is 25 less than the preceding value.

Note that the value in cell A5 is incorrect. Although it is very close to zero (0.0000000000000284217), it is not actually zero.

Additional query words: XL5 XL7 XL97 XL98 5.00a 5.00c 7.00a

Keywords : xlformula
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbbug