Article ID: 151515 - View products that this article applies to.
This article was previously published under Q151515
The INDEX function in a PivotTable calculates a weighted average by using the following formula:
When you use PivotTables to compare the relative importance of your row data versus your column data, it can be extremely helpful to display your data as an Index. For example, this article demonstrates that if you display data in the Index format instead of a "Percentage of Total" format, you can often reveal valuable comparisons that you might not otherwise see.
The first section of this article describes how to create two PivotTables: one that displays sales figures as a Percentage of Total and another that displays sales figures as an Index. The second section of this article ("Analysis of the Results"), explains the advantages of being able to compare data displayed both ways.
To create the sample worksheet and PivotTables, follow these steps:
Analysis of the ResultsExample 1:
If you compare the sales of bananas across the states, the Percentage of Total figures show that slightly more bananas were sold in California (8.01% in cell F4) than were sold in Texas (7.93% in cell H4).Example 2:
However, if you look at the Index data, you can see that bananas are much more important to the Texas market (1.38 in cell H13) than they are to the California market (0.92 in cell F13)
If you compare the sales of bananas in California to the sales of kiwi in Pennsylvania, the Percentage of Total figures show that significantly more bananas were sold in California (8.01% in cell F4) than kiwis in Pennsylvania (6.94% in cell G6).
However, if you look at the Index data, you can see that kiwis are more important to the Pennsylvania market (1.06 in cell G15) than bananas are to the California market (0.92 in cell F13).
Microsoft Excel 97For more information about creating PivotTables, click the Index tab in Microsoft Excel Help or MS Excel Help, type the following text
PivotTables, creatingand then double-click the selected text to go to the "Create a PivotTable" topic.
Microsoft Excel 7.0For more information about PivotTables in Microsoft Excel 7.0, click Answer Wizard on the Help menu and type:
Microsoft Excel 5.0For more information about PivotTables in Microsoft Excel 5.0, click the Search button in Help and type:
Article ID: 151515 - Last Review: August 17, 2005 - Revision: 1.3
Retired 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.