Skip to content

04-03: Descriptive Statistics Dashboard

Statistics & Probability

View the live site — ijk37.com

Project 03

Home  |  All Projects  |  Notes  |  Exercises  |  Quiz Hub

Produce a full descriptive summary of an employee data set — centre, spread, position, shape, and outliers — broken out by department, and turn it into a one-page dashboard.

Chapters applied: 03-01 · 03-02 · 04-01 · 04-02

Difficulty: ⭐⭐ Beginner–Intermediate

The Data

data/employees.csv — 80 employees across 4 departments.

Column Level Notes
employee_id Nominal Unique key
department Nominal Sales, Engineering, Support, Marketing (20 each)
gender Nominal F / M / X
years_service Ratio —
salary Ratio Contains one genuine high earner for the outlier work
rating Ratio (1–5) Performance rating

What You Produce

  1. A summary table for salary: n, mean, median, mode, SD, variance, CV, min, Q1, Q3, max, IQR, skewness
  2. The same table by department, plus a check that the weighted mean of the four departmental means reproduces the overall mean
  3. Outlier detection by both the 1.5 × IQR rule and the |z| > 3 rule, with a note on where they disagree
  4. Z-scores for every employee, and a "most unusual" list
  5. A dashboard sheet/figure: histogram, boxplot by department, and the summary table

Excel Route

Step 1 — Overall summary

Data ▸ Data Analysis ▸ Descriptive Statistics, Input Range = salary, tick Summary statistics and Confidence Level for Mean: 95%. You immediately get mean, standard error, median, mode, SD, variance, kurtosis, skewness, range, min, max, sum, count.

Then add what the ToolPak leaves out:

=QUARTILE.INC(E2:E81,1)                                  ' Q1
=QUARTILE.INC(E2:E81,3)                                  ' Q3
=QUARTILE.INC(E2:E81,3)-QUARTILE.INC(E2:E81,1)           ' IQR
=STDEV.S(E2:E81)/AVERAGE(E2:E81)*100                     ' CV as a percent
=3*(AVERAGE(E2:E81)-MEDIAN(E2:E81))/STDEV.S(E2:E81)      ' Pearson SK
=TRIMMEAN(E2:E81, 0.1)                                   ' 5% trimmed from each end

Step 2 — By department

Insert ▸ PivotTable: Rows = department; drag salary into Values five times and set each to Count, Average, StdDev, Min, Max.

The PivotTable cannot give you the median or the quartiles, so add them beside it with array formulas:

=MEDIAN(IF($B$2:$B$81=$H2, $E$2:$E$81))                  ' Ctrl+Shift+Enter pre-365
=QUARTILE.INC(IF($B$2:$B$81=$H2, $E$2:$E$81), 1)
=QUARTILE.INC(IF($B$2:$B$81=$H2, $E$2:$E$81), 3)
=AVERAGEIF($B$2:$B$81, $H2, $E$2:$E$81)                  ' no array needed
=COUNTIF($B$2:$B$81, $H2)

Step 3 — Verify the weighted mean

The overall mean must equal the weighted mean of the departmental means, not their plain average:

=SUMPRODUCT(departmental_means, departmental_counts)/SUM(departmental_counts)
=AVERAGE(E2:E81)                        ' these two MUST agree
=AVERAGE(departmental_means)            ' this one will NOT, unless every n is equal

Here every department has exactly 20 people, so all three agree — change one department's size and watch the third value break. That is the lesson.

Step 4 — Outliers

' fences  (put the two results in $H$1 and $H$2)
=QUARTILE.INC($E$2:$E$81,1)-1.5*(QUARTILE.INC($E$2:$E$81,3)-QUARTILE.INC($E$2:$E$81,1))
=QUARTILE.INC($E$2:$E$81,3)+1.5*(QUARTILE.INC($E$2:$E$81,3)-QUARTILE.INC($E$2:$E$81,1))

=IF(OR(E2<$H$1, E2>$H$2), "IQR OUTLIER", "")             ' flag column
=STANDARDIZE(E2, AVERAGE($E$2:$E$81), STDEV.S($E$2:$E$81))   ' z-score
=IF(ABS(F2)>3, "Z OUTLIER", "")                          ' the other rule

Compare the two flag columns. The IQR rule catches the high earner; the z-rule may not, because that one value inflates s and hides itself — the masking effect from 04-02.

Step 5 — Dashboard

On a clean sheet, place:

  • Insert ▸ Insert Statistic Chart ▸ Histogram of salary
  • Insert ▸ Insert Statistic Chart ▸ Box and Whisker with salary grouped by department (select both columns)
  • The summary table, formatted with Home ▸ Conditional Formatting ▸ Data Bars on the mean column
  • A slicer: click the PivotTable ▸ PivotTable Analyze ▸ Insert Slicer ▸ gender, so the whole dashboard filters live

R Route

Rscript r/analysis.R

Prints every table and writes dashboard.png. See r/analysis.R.

Python Route

python python/analysis.py

Same output via pandas + matplotlib. See python/analysis.py.

Checkpoints

  • The summary reports both mean/SD and median/IQR, and says which pair to trust for this data
  • The weighted mean of the departmental means reproduces the overall mean
  • Both outlier rules are applied, and any disagreement is explained
  • Skewness is reported and matches what the histogram shows
  • The CV is used for any across-department comparison of variability

Extend It

  • Add salary_per_year_of_service and describe it. Is it more or less variable than raw salary (compare CVs)?
  • Recompute every statistic with and without the high earner and tabulate the change. Which measures barely move?
  • Build the grouped frequency table for salary and compute the grouped mean and SD (03-02); report the approximation error
  • Standardize salary within each department and find the most unusual employee relative to their own team