Help and Support

Article ID: 185056 - Last Review: January 22, 2007 - Revision: 1.1

WD97: Incorrect Formula Results Using ABOVE, BELOW, LEFT, RIGHT

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q185056

On This Page

Expand all | Collapse all

SYMPTOMS

When you use a formula field in a table, the result is incorrect if the following are true:

  • You use ABOVE, BELOW, LEFT, or RIGHT as an argument for the field.

    -and-
  • The defined range contains noncontiguous values (one or more blank cells between two values).
For example, the formula in cell A6 of the following table shows a result of 7 instead of 12: 
   |------------------|
   |               3  | <(Cell A1)
   |------------------|
   |                  | <(Cell A2)
   |------------------|
   |               4  | <(Cell A3)
   |------------------|
   |                  | <(Cell A4)
   |------------------|
   |               5  | <(Cell A5)
   |------------------|
   |   {=SUM(ABOVE)}  | <(Cell A6)
   |------------------|
Back to the top

CAUSE

Word calculates all values correctly up to and including the first cell after an empty table cell, and then stops.
Back to the top

WORKAROUND

To work around this problem, use either of the following methods.
Back to the top

Method 1: Type Zero (0) in Any Blank Cells

Type zero (0) in any blank cells included within the calculation range, for example: 
   |------------------|
   |               3  | <(Cell A1)
   |------------------|
   |               0  | <(Cell A2)
   |------------------|
   |               4  | <(Cell A3)
   |------------------|
   |               0  | <(Cell A4)
   |------------------|
   |               5  | <(Cell A5)
   |------------------|
   |   {=SUM(ABOVE)}  | <(Cell A6)
   |------------------|
Back to the top

Method 2: Use Cell References

For a range that contains one or more blank cells, use cell references to define the range instead of ABOVE, BELOW, LEFT, or RIGHT. For example, use the following formula field to obtain a correct result: 
   |------------------|
   |               3  | <(Cell A1)
   |------------------|
   |                  | <(Cell A2)
   |------------------|
   |               4  | <(Cell A3)
   |------------------|
   |                  | <(Cell A4)
   |------------------|
   |               5  | <(Cell A5)
   |------------------|
   |   {=SUM(A1:A5)}  | <(Cell A6)
   |------------------|
Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Back to the top

MORE INFORMATION

For additional information, please see the following articles in the Microsoft Knowledge Base:
118521  (http://support.microsoft.com/kb/118521/EN-US/ ) WD: SUM Formula Does Not Update Correctly

105639  (http://support.microsoft.com/kb/105639/EN-US/ ) WD: Table Formula SUM(Above) Adds Number from Heading Row

113980  (http://support.microsoft.com/kb/113980/EN-US/ ) WD: Wrong Calculation Result with Bookmarks in Table
Back to the top
APPLIES TO
  • Microsoft Word 97 Standard Edition
Back to the top
Keywords: 
kbbug kbformula kbpending kbtable KB185056
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations