Excel 5: Descriptive Statistics for Qualitative and Quantitative Variables

Cross Tables and Relationship Assessments Between Two Qualitative Variables

  • Conceptual Overview

    • Evaluating relationships between qualitative variables, such as Gender and Employed, involves creating cross tables (also known as contingency tables) and visualizing them with 100% stacked column charts.

    • The dataset used for these procedures is sociodemographics1.xlsx.

    • The variable Employed has numerical outcomes where 0 represents "no" and 1 represents "yes".

    • The variable Gender has numerical outcomes where 0 represents "female" and 1 represents "male".

  • Establishing Cross Table Structure for Absolute Frequencies

    • Row headers: Place the header employed in cell I5. Outcomes 0 and 1 for the variable and the header total reside in cells J5:L5. Descriptive labels "no" and "yes" are entered in J6:K6.

    • Column headers: Place the header gender in cell H6. Outcomes 0 and 1 and the header total reside in the range H7:H9. Descriptive labels "female" and "male" are entered in range I7:I8.

    • Frequency Formula: To calculate absolute frequencies, use the COUNTIFS function with a mix of absolute and relative cell referencing. In cell J7, enter:         =COUNTIFS($B$2:$B$51; $H7; $D$2:$D$51; J$5)

    • This formula can be copied across the range J7:K8.

    • Row Totals: In cell L7, use the formula =SUM(J7:K7)=SUM(J7:K7) and copy it to cell L8.

    • Column Totals: In cell J9, use the formula =SUM(J7:J8)=SUM(J7:J8) and copy it to the range K9:L9.

  • Cross Tables Based on Relative Frequencies

    • General Relative Frequencies: To determine the proportion of the total sample in each cell, copy the absolute frequency table to cell P5. In cell R7, calculate the proportion relative to the total sample size (L9L9) using:         =J7/$L$9

    • Conditional Frequencies (Row Proportions): To understand the relationship conditioned on gender, divide each cell frequency by its respective row total. In cell J16, use:         =J7/$L7

    • Conditional Frequencies (Column Proportions): To understand the relationship conditioned on employed, set up a similar table in cells P14:S18, dividing frequencies by column totals.

Visualization with 100% Stacked Column Charts

  • Chart Creation from Absolute Frequencies

    • Select the range I6:K8 and navigate to the Insert tab, then select Chart > Column.

    • Convert to Stacked format: In the Chart Design tab, click Change Chart Type, select Column, and choose the 100% Stacked Column subtype.

    • X-Axis Customization: Use the Switch Row/Column command in the Chart Design tab to change which variable is plotted on the horizontal axis.

    • Legend Editing: Right-click the graph, choose Select Data, select the series (e.g., "no" or "yes"), click Edit, and type descriptive names (e.g., "not employed" or "employed").

    • Data Labels: To add labels to the centers of the columns, navigate to Chart Design > Add Chart Elements > Data Labels > Center. Note that if these are added to a chart based on absolute frequencies, they will display the count rather than the proportion.

  • Proportional Data Labels

    • In Microsoft Excel 365, labels cannot be immediately converted from absolute frequencies to row proportions within the chart settings.

    • To display row proportions as labels, the chart must be created directly from the row proportions frequency table (range I15:K17).

    • To ensure descriptive labels like "not employed" and "employed" appear on the x-axis, either change the labels in cells R15:S15 or use Select Data and replace the Horizontal (Category) axis labels with the array: ="not employed"\"employed".

Advanced Data Analysis Using PivotTables

  • PivotTable Setup

    • Initial Data Preparation: It is helpful to add an identification column. In cell A1, enter the header id. Enter 1 in A2 and 2 in A3. Use the Fill handle to drag the pattern down to cell A51 to number 50 individuals.

    • Creating the Table: Select cells A1:F51, go to Insert > PivotTable, and choose an Existing Worksheet at cell H3.

    • Configuring Fields:

      • Drag gender to the Rows area.

      • Drag employed to the Columns area.

      • Drag id to the Values area.

      • In the Value Field Settings (via the drop-down arrow next to the variable in the Values area), change the summarization from Sum to Count.

    • Calculating Proportions: Within Value Field Settings, navigate to the Show Data As tab and select % of Row Total.

  • Dynamic PivotTable Features

    • Filtering: Drag a variable (e.g., employed) into the Filters area of the PivotTable Fields pane. Use the drop-down arrow in the resulting filter cell (e.g., I31) to select specific criteria, such as "yes".

    • Changing Conditioned Variables: To switch the analysis (e.g., from employed to children), deselect employed in the field list and drag children to the Columns area. The table and any linked 100% column charts will update automatically.

    • Grouping Outcomes: Outcomes can be grouped by selecting them (e.g., outcomes 3 and 4 in cells L62:M62), right-clicking, and choosing Group. The grouped header (e.g., Group1) can be renamed to a descriptive label like "at least 3".

    • Data Separation for Grouping: To ensure changes to a copied PivotTable do not affect the original, change the data source slightly (e.g., from A1:F51 to A1:F52) via the Change Data Source button in the PivotTable Analyze tab.

Relationships Between Qualitative and Quantitative Variables

  • Data Preparation for Side-by-Side Boxplots

    • Excel requires quantitative data for different qualitative outcomes to be in separate columns to generate specific boxplots (e.g., one column for Female IQ and one for Male IQ).

    • Manual Method: Copy outcomes for Gender and IQ to G1:H51. Use Sort & Filter > Custom Sort to sort the data by gender. Manually copy IQ values belonging to each gender group into separate columns labeled "Female" and "Male".

    • Automated Method using FILTER:

      • For Female (gender 0): =FILTER(E2:E51;B2:B51=0;1)=FILTER(E2:E51; B2:B51=0; 1)

      • For Male (gender 1): =FILTER(E2:E51;B2:B51=1;1)=FILTER(E2:E51; B2:B51=1; 1)

    • Note: If the qualitative variable uses text labels rather than numerical codes, Excel can often generate the plot directly from the two original columns.

  • Generating and Formatting Boxplots

    • Select the prepared separate columns (e.g., J1:K30) and select Insert > Chart > Box and Whisker.

    • Quartile Calculation: By default, Excel uses the Exclusive Median (=QUARTILE.EXC=QUARTILE.EXC). To match standard inclusive calculations, right-click the data series, select Format Data Series, and choose Inclusive Median (=QUARTILE.INC=QUARTILE.INC).

    • Clean-up: Remove the chart title, add a y-axis title, remove the number "1" from the x-axis, and add a legend.

  • Summary Statistics by Qualitative Category

    • Statistical measures provide precise numerical comparisons to supplement the visual information in a boxplot.

    • Average with Criteria: Use the AVERAGEIF function:         =AVERAGEIF($B$2:$B$51; O24; $E$2:$E$51)

    • Standard Deviation with Criteria: Since STDEV.S does not have a built-in criteria version, nest an IF statement within it:         =STDEV.S(IF($B$2:$B$51=O24; $E$2:$E$51))

    • Quartiles with Criteria: Use a nested IF command within the QUARTILE.INC function:         =QUARTILE.INC(IF($B$2:$B$51=O$24; $E$2:$E$51); $N25)

    • Note: In these formulas, O24 typically refers to the qualitative outcome (e.g., 0 or 1) and N25 refers to the specific quartile required (e.g., 1, 2, or 3).