Analyse-it
You are viewing part of the Analyse-it for Microsoft Excel user-guide. When you have read this topic we recommend you view contents of the user-guide or you can read more about Analyse-it, our statistical analysis software for Microsoft Excel.
Topic: Using Analyse-it > Preparing data in Excel > 2-way Table dataset > Summarising counts in a 2-way Table dataset

Summarising counts in a 2-way Table dataset

ADVANCED

Many tests involving two categorical variables only require the count (frequency) of observations in each group, rather than the actual observations. The counts for the groups can be arranged using a 2-way table dataset, with counts entered instead of the observations.

EXAMPLE  The two-way table dataset below contains counts of subjects classified by two factors, Drug: A, B & C, and Sex: M & F. The groups for Drug are placed on first row of the dataset, the groups for the factor Sex on the first column of the dataset, and the counts in the cells.

Using two Header rows

As for the 2-way table dataset, using a single row of headers does not allow the names of the variables to be specified.

The solution is to use an additional header row to hold the names of the variables. The name of the factor across the top of the columns is placed above the first group on the columns, and the name of the factor down the left of the rows is placed in the cell above the first row group. Analyse-it will then pick-up the names of the variables.

EXAMPLE  The dataset below is the same as before except an extra row has been added to hold the variable names. The name of factor on the columns Drug is placed above the first group A, and the name of the factor on the rows Sex is placed above the group M.

When the dataset is arranged on the Excel worksheet the next step is to define the range as a dataset.

To define the dataset:

  1. Select a cell in the range containing the dataset.
  2. Click Define Dataset on the Analyse-it tab/toolbar to show the Define Dataset wizard.
  3. Click Next>.
  4. Click 2-way table for the layout.
  5. Click Next>.
  6. Set the Header rows to 1 if a single row (called the headers) contains the names of the groups, or set to 2 if two rows contain the variable and group names.
  7. Click Counts to indicate the dataset contains counts (frequencies).
  8. Tick Apply Autoformat to apply cell formatting to the dataset to indicate the cells Analyse-it assumes contain variable names and the cells it assumes contain data. Clear the checkbox to leave your own formatting intact.
  9. Click Next>.
  10. Click Finish to close the Define Dataset wizard.

Part of the Analyse-it for Microsoft Excel user guide. For more information about Analyse-it, the statistics add-in for Microsoft Excel, or to download a free 30-day trial please visit http://www.analyse-it.com/. © Analyse-it Software, Ltd. Analyse-it® is a registered trademark. Microsoft® Excel® is a registered trademark of Microsoft.