Excel 5: Descriptive Statistics for Assessing Relationships
Cross Tables and Frequency Distributions for Qualitative Variables
Investigating the relationship between two qualitative variables, such as Gender and Employed (available in the dataset), begins with the construction of cross tables.
Cross tables can be structured based on absolute frequencies, relative frequencies, or conditional relative frequencies.
Absolute Frequency Cross Table Structure
Row Header: Place the variable header (e.g., employed) in a designated cell (I5).
Outcomes: Enter the numerical outcomes for the variable below the header (e.g., 0 and 1 represent the states of being employed). Include a "total" header.
Labels: For clarity, type descriptive labels such as "no" and "yes" next to the numeric outcomes.
Column Header: Place the second variable header (e.g., gender) in a designated cell (H6).
Outcomes for Columns: Enter the numerical outcomes (e.g., 0 and 1) in adjacent rows. Labels such as "female" and "male" should be added for these outcomes.
Formulas for Absolute Frequencies
To count occurrences across two conditions, use the
function with absolute and relative cell referencing.An example formula to calculate frequencies for specific gender and employment statuses:
Apply this logic across the range J7:K8.
Row Totals: Use the sum function, for example: .
Column Totals: Use the sum function, for example: .
Relative Frequency Cross Table
Relative frequencies are calculated by dividing individual cell frequencies by the grand total.
Copy the structure of the absolute frequency table.
Replace absolute values with a division formula referencing the grand total (e.g., cell L9):
This formula can then be copied across the relevant range (R7:S8).
Conditional Relative Frequencies
These tables represent the relationship based on one variable's total (row or column proportions).
Conditioned on Gender (Row Proportions): Divide the absolute frequency by the row total:
Conditioned on Employed (Column Proportions): Divide the absolute frequency by the column total using the structure from cells P14:S18.
Visualization with 100% Stacked Column Charts
A 100% stacked column chart is the standard graphical representation for comparing relative frequencies between groups.
Creating the Chart
Select the descriptive labels and absolute frequencies (e.g., I6:K8).
Navigate to the Insert tab and select Chart > Column.
To convert to the specific type: Select the graph, go to Chart Design, click Change Chart Type, and choose 100% Stacked Column.
Use the Switch Row/Column command in the Chart Design tab to toggle which variable is represented on the x-axis vs. the segments of the columns.
Chart Refinement
Positioning: For organizational purposes, move the chart to a specific cell (e.g., H23).
Legend Editing: Right-click the chart and select Select Data. Edit series names (e.g., change "no" to "not employed" and "yes" to "employed") to ensure the legend is descriptive.
Data Labels: Navigate to Chart Design > Add Chart Elements > Data Labels > Center. This displays the values directly on the columns.
Software Specifics (Excel 365)
In Microsoft Excel 365, it is not possible to immediately change absolute frequency labels to row proportions directly on a chart generated from an absolute frequency table.
To show row proportions as labels: Create a new graph using the row proportions frequency table (e.g., I15:K17) and apply the 100% Stacked Column type.
Descriptive X-Axis: If numeric values appear on the x-axis, replace them using the Select Data dialog. Under Horizontal (Category) axis labels, enter the desired labels in an array format: .
Utilizing PivotTables for Multiple Variables
PivotTables provide a dynamic way to analyze relationships between multiple variables simultaneously.
Data Preparation for PivotTables
Adding an Identifier: It is helpful to add an "id" column to uniquely number individuals. Type 1 and 2 in the first two rows (A2, A3) and use the Fill handle to drag the series to the end of the dataset (e.g., A51 for 50 individuals).
Building the PivotTable
Select the data range including headers (e.g., A1:F51).
Insert a PivotTable into an Existing Worksheet at a specific cell (e.g., H3).
Configuration:
Drag the Gender variable to the Rows area.
Drag the Employed variable to the Columns area.
Drag the "id" variable to the Values area.
Value Field Settings: Change the calculation from "Sum" to "Count" to reflect absolute frequencies.
Advanced PivotTable Settings
To view percentages instead of counts: Open Value Field Settings > Show Data As and select % of Row Total. This calculates row proportions automatically.
Renaming: Titles within the PivotTable (like Row Labels or Column Labels) can be manually overwritten with descriptive names (e.g., "female", "male", "yes", "no").
Interactivity: Filtering and Grouping
Filtering: Drag a variable (e.g., employed) to the Filters area in the PivotTable Fields pane. Alternatively, use the drop-down arrows next to row/column headers to filter for specific outcomes.
Grouping: Select specific outcomes in the PivotTable (e.g., outcomes 3 and 4 for children), right-click, and select Group. This collapses multiple categories into a single group (e.g., "at least 3").
Data Source Management: To prevent changes in one PivotTable from affecting a copy, use the Change Data Source command to slightly alter the range or identifiers so Excel treats it as a distinct set.
Analysis of Qualitative and Quantitative Variables
Relationships between a qualitative variable (e.g., Gender) and a quantitative variable (e.g., IQ) are typically assessed using side-by-side boxplots and summary statistics split by group.
Data Preparation for Boxplots
Boxplots in Excel often require data for different groups to be placed in separate columns.
Sorting Method: Copy Gender and IQ data, perform a Custom Sort by the gender variable, then copy the IQ values for each gender into separate columns labeled "Female" and "Male".
Automated Filtering Method: Use the
function to extract values based on criteria.(Extracts IQ where gender equals 0).
(Extracts IQ where gender equals 1).
Generating Side-by-Side Boxplots
Select the split data columns (including headers).
Insert a Box and Whisker chart.
Quartile Calculation: By default, Excel uses Exclusive Median (). To change this, select Format Data Series and choose Inclusive Median ().
Formatting: Remove the chart title, add a y-axis title (e.g., IQ), remove generic x-axis labels, and add a legend for the groups.
Numerical Summary Statistics by Group
Specific measures help quantify the differences visualized in the boxplot.
Mean: Use the
function to calculate the average of a range based on a specific criteria.Standard Deviation and Quartiles: Since there are no built-in "IF" versions of these functions, combine the base function with an
statement.Quartiles:
Standard Deviation: