Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. The result of a calculation that uses the data in a merged cell does not match the result that is expected based on the visible data in the merged cell in Excel 2000, in Excel 2002, in Excel 2003, in Office Excel 2007, or in Excel 2010

The result of a calculation that uses the data in a merged cell does not match the result that is expected based on the visible data in the merged cell in Excel 2000, in Excel 2002, in Excel 2003, in Office Excel 2007, or in Excel 2010

View products that this article applies to.

Symptoms

Assume that you paste a range of cells as formulas into a merged cell in Microsoft Excel 2000, in Excel 2002, in Excel 2003, in Excel 2007, or in Excel 2010. In this scenario, the paste may apply the formula to each underlying cell of the merged cell, if the source range is not also a merged cell of the same size. Excel then calculates the result by using all the formulas in the underlying cells of the merged cell. This is expected. However, because you only see one of the component cells of the merged cell, the calculation result might differ from the result that you expect. Additionally, the results of certain aggregation functions, such as the Sum aggregation, as displayed in the status bar do not match the expected results based on the visible data.

↑ Back to the top

Cause

This problem occurs because only the data that appears in the upper-left cell of the copied cells is visible in the merged cell.

↑ Back to the top

Workaround

To work around this problem, use one of the following methods:
  • Use the Paste feature to paste the data.
  • Unmerge the cell before you paste the formulas of the copied data. Then, merge the cells.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

More Information

Steps to reproduce the problem

  1. Create a new workbook in Office Excel 2007.
  2. Right-click the status bar, and then select the Sum aggregation.
  3. Enter 5 in the A1 cell and in the D2 cell.
  4. In the A3 cell, type =SUM(A2,B2,C2,D2).
  5. Select the A2, B2 and C2 cells.
  6. On the Home tab, click Merge & Center.
  7. Right-click the A1 cell, and then click Copy.
  8. Right-click the merged cell, and then click Paste Special.
  9. In the Paste Special dialog box, click Formulas, and then click OK.
20 is displayed in the A3 cell. However, the value that is expected based on the visible data in the merged cell is 10. Additionally, if you select the merged cell (A2), you can see that the value of the Sum aggregation in the status bar is 15. This occurs because the merged cell is an aggregate of the cells A2, B2 and C2, and these cells contain the same formula value of 5.

↑ Back to the top

Keywords: consumeroff2010track, kbtshoot, kbsurveynew, kbprb, kb

↑ Back to the top

Article Info
Article ID : 983435
Revision : 3
Created on : 3/30/2017
Published on : 3/30/2017
Exists online : False
Views : 273