Microsoft KB Archive/89501

{|
 * width="100%"|

Excel: Index Option Using Crosstab ReportWizard

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0
 * Microsoft Excel for the Macintosh, version 4.0

-

SUMMARY
In version 4.0 of Microsoft Excel, the Crosstab ReportWizard contains an option for the Value field called Index which is not documented.

MORE INFORMATION
The formula used for creating the Index is as follows:

  Index = (Value/Row_total)/(Column_total/Grand_total)

The definitions of the components of index are as follows:

  Value=the value of the cell whose index you are computing.

Row_total=the total for the row in which the value whose index you are computing exists

Column_total=the total for the column in which the value whose index you are computing exists

Grand_total=the grand total of all the column (or row values)

Example
Assume this is your database:

A1: Name  B1: Answer  C1: Dues A2: John  B2: Yes     C2: 25 A3: Mark  B3: Yes     C3: 25 A4: Kevin B4: No      C4: 50 A5: David B5: No      C5: 50

To create the Crosstab, choose Crosstab from the Data menu. Select &quot;Name&quot; for the Row Categories. Select &quot;Answer&quot; for the Column Categories. Select &quot;Dues&quot; for the Value fields and choose Options. Select Values and Index.

The index value for the Name &quot;David&quot; and the Answer &quot;No&quot; is 1.5, calculated as follows:

Value = 50

Row_Total = 50

Column_Total = 100

Grand_Total = 150

Index (50/50)/(100/50) = 1.5