Module III: Descriptive Analytics
This module introduces learners to descriptive analytics as a foundation of data interpretation
and decision-making. It covers basic statistical measures such as mean, median, mode, standard
deviation, and variance. Students will use Excel functions and the Data Analysis ToolPak to
perform descriptive analysis, including hypothesis testing and t-tests, preparing them for further
statistical and data-driven techniques.
1. Learning Outcomes
By the end of this module, students should be able to:
1. Explain the purpose and scope of descriptive analytics.
2. Calculate and interpret key statistical measures such as mean, median, mode, and
standard deviation.
3. Use built-in Excel functions and the Data Analysis ToolPak to summarize datasets.
4. Conduct basic hypothesis testing and t-tests using Excel.
2. Lesson Structure and Content
A. Overview of Descriptive Analytics
Descriptive analytics summarizes historical data to uncover patterns, trends, and
relationships.
It forms the foundation of business intelligence and decision-making.
It answers “What happened?” by using statistical summaries and visualizations.
Examples:
o Monthly sales trends
o Average customer satisfaction scores
o Total website visits in a quarter
B. Descriptive Analytics on Datasets (Mean, Median, Mode, Standard Deviation, etc.)
Key Concepts:
Measure Description Excel Functions:
Mean Average of data values =AVERAGE(range)
Median Middle value in an ordered dataset =MEDIAN(range)
Mode Most frequently occurring value =MODE.SNGL(range)
Standard Measure of spread/dispersion around =STDEV.S(range)
Deviation the mean
Variance Average of squared differences from =VAR.S(range)
the mean
Range Difference between highest and =MAX(range)-MIN(range)
lowest values
Example Dataset:
Student Score
A 90
B 85
C 87
D 92
E 84
C. Advanced Data Analysis Tool
i. Excel Functions for Data Analysis
Focus Functions:
Logical: IF(), COUNTIF(), SUMIF()
Lookup: VLOOKUP(), HLOOKUP(), INDEX(), MATCH()
Statistical: AVERAGE(), STDEV.S(), VAR.S(), MEDIAN()
ii. Data Analysis ToolPak
An Excel add-in providing tools for statistical and engineering analysis.
How to Enable:
File → Options → Add-Ins → Manage Excel Add-ins → Check "Analysis ToolPak"
Key Tools:
o Descriptive Statistics
o Correlation
o Regression
o t-Test (Paired/Two Sample)
iii. Conducting Hypothesis Tests and t-Tests
Key Concepts:
Hypothesis testing determines whether observed results are statistically significant.
o Null Hypothesis (H₀): Assumes no effect or difference.
o Alternative Hypothesis (H₁): Assumes a significant effect or difference.
Types of t-tests:
Test Used When
One-sample t-test Comparing sample mean to known population mean
Two-sample t-test Comparing means of two independent groups
Paired sample t-test Comparing two related groups (before/after)
Excel Tool:
Use Data Analysis ToolPak > t-Test: Two-Sample Assuming Equal Variances
Example:
Compare average exam scores of two classes to see if teaching methods differ.
Summary/Key Takeaways
Descriptive analytics helps organizations understand what has happened in the past.
Key measures include central tendency (mean, median) and dispersion (std. deviation,
range).
Excel’s built-in functions and ToolPak simplify analysis and statistical testing.
