Inferential and Descriptive Statistical Formulas in Excel

Name: Inferential and Descriptive Statistical Formulas in Excel
Rating: 4.4 (2 reviews)

Master Excel formulas to analyze data, calculate probabilities, and perform statistical testing.

Created byCreateMyExcel with Ben Scholl

Last updated 7/2025

English

English [Auto],

What you'll learn

Learn to use COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS to summarize data.
Calculate averages using AVERAGE, MEDIAN, MODE, and weighted mean formulas.
Find extreme values using MAX, MIN, LARGE, and SMALL functions.
Perform top and bottom "k" value calculations for focused analysis.
Rank data and determine percentiles using Excel’s statistical tools.
Calculate range, variance, and standard deviation to measure variability.
Build frequency distributions to analyze data spread and groupings.
Use formulas to extract periodic or random samples from large datasets.
Test relationships between variables using covariance and correlation.
Explore probability, confidence intervals, and hypothesis testing basics.

Course content

2 sections • 42 lectures • 2h 4m total length

Building Descriptive Statistical Formulas3:48
Master building descriptive statistical formulas in Excel, applying counting functions, mean, median, and mode, extremes, range, percentile, and standard deviation to support practical business analysis.
Counting Items & COUNT Function1:15
Learn how to use Excel's count function to tally numeric values in a data set, excluding text, dates, logical values, and errors, with a practical column D example.
COUNTA Function1:25
Explore the count a function in Excel to count all non-blank values in a range, counting numbers, text, and other data types.
COUNTBLANK Function0:55
Count blank function counts blank values in a data set, demonstrated with the range A4:A27 in Excel, and you can apply it to your descriptive statistics analysis.
COUNTIF Function1:48
Explore the Excel countif function, counting items in a range using a criteria such as greater than or equal to 100, with numeric or text options, in descriptive statistical formulas.
COUNTIFS Function1:49
Learn to apply the countifs function to evaluate multiple criteria across cost and quantity columns, counting items that meet both conditions, such as cost > 30 and quantity < 50.
Calculating Averages & AVERAGE Function0:57
Calculate the average in Excel using the average function on D4:D27 to get 61.75, and practice rounding or nesting the average on the descriptive statistics worksheet.
AVERAGEIF Function1:56
Learn how the averageif function in Excel computes the mean of values meeting a criterion, illustrated by items with cost greater than 25 and average quantity on hand.
AVERAGEIFS Function2:24
Learn how to use the averageifs function to compute the mean of items that meet multiple criteria, such as cost between 30 and 50, and summarize items on hand.
MEDIAN Function1:25
Explore how the median marks the middle value and splits data so 50% are above and 50% below, as in a 50.5 example, using Excel's median function.
The MODE Function1:02
Identify the mode as the most frequent value in a data set, explore single mode and multi mode for ties, and practice the mode function in Excel.
Calculating the Weighted Mean2:14
Compute a weighted mean by multiplying each value by its weight, summing the products, and dividing by the total weights using Excel’s sumproduct to weight profit margins by sales volume.
Calculating Extreme Values with MAX & MIN Functions1:27
Learn how to find the largest and smallest values in a data set with Excel's max and min functions, and use these descriptive statistics to understand data range.
The LARGE and SMALL Functions2:03
Learn to identify rank positions in data using the large and small functions in Excel; specify the array and kth rank to extract the nth largest or smallest value.
Performing Calculations on the Top K Values2:18
Learn to compute descriptive statistics in Excel by nesting the large function inside average to get the top k values, such as the top five from a quantities column.
Performing Calculations on the Bottom K Values1:23
Learn to calculate the sum of the bottom k values by nesting the small function inside the sum function in Excel, using the three smallest as a concrete example.
Calculating Rank2:03
Explore how to calculate an item's rank against a data set using Excel rank functions. Compare rank.avg and rank.eq, and see how duplicates are handled.
Calculating Percentile1:30
Learn how to calculate percentiles in Excel using percentile.x, choosing inclusive or exclusive options, and interpret the 90th percentile from a sample dataset.
Calculating Measures of Variation Overview & Range Function2:55
Explore measures of variation and dispersion, focusing on the range as max minus min. Learn to compute the range in Excel using max and min to assess data spread.
Calculating The Variance2:20
Variance measures how data vary from the mean by squaring deviations, summing squares, and dividing by the number of values. In Excel, use var.p for population variance.
Calculating The Standard Deviation2:18
Explore how to use standard deviation in excel to gauge data spread and variance from the mean, including population versus sample stdev and outliers.
Working with Frequency Distributions1:50
Explore how to build frequency distributions in Excel by grouping data into bins, using the frequency function, and interpreting the resulting counts for each category.

Building Inferential Statistical Formulas4:19
Explore how inferential statistics in excel extend beyond descriptive analysis to infer broader population trends. Build formulas that assess uncertainty, sampling, relationships, and forecasting with confidence intervals and hypothesis testing.
Understanding Inferential Statistics4:04
Understand inferential statistics, sampling, and uncertainty, and identify independent, dependent, and random variables, including discrete and continuous data, to forecast with regression in Excel.
Sampling Data & Extracting a Periodic Sample7:04
Learn how inferential statistics use sampling to infer about a population, including periodic sampling that selects every nth observation and random sampling in Excel with the offset formula.
Extracting a Random Sample3:49
Generate random samples in Excel using the offset and randbetween functions to pull data from a population, excluding headers, and practice on the samples worksheet.
Determining whether Two Variables are Related2:22
Determine whether two variables relate by using covariance and correlation to gauge direction and strength, as part of inferential statistics, and learn how to measure these associations in Excel.
Calculating Covariance2:27
Explore how covariance shows whether two variables move together or apart, with positive indicating a direct relationship, using sample covariance (covariance.s) in Excel on advertising spend and sales.
Calculating Correlation2:53
Learn how correlation measures the strength and direction of relationships between two variables using Excel's correl function on two arrays, illustrated by a 0.81 correlation between advertising and sales.
Working with Probability Distributions & Calculating Probability11:10
Explore probability concepts and probability distributions, then apply Excel tools like frequency and the prob function to calculate and interpret outcomes, from coin tosses to exam grades.
Discrete Probability & Binomial Distribution3:35
Explore discrete probability through the binomial distribution in Excel, using Binomdist to calculate the probability of a specific number of successes across independent trials with two mutually exclusive outcomes.
Discrete Probability & Hypergeometric Distribution3:18
Explore discrete probability through the hypergeometric distribution in Excel, showing how to model draws without replacement from a finite population and compute specific success probabilities.
Discrete Probability & Poisson Distribution1:51
Learn the Poisson distribution for discrete events over time, using an average rate to calculate the probability of a given number of independent events in a time interval.
Understanding the Normal Distribution and THE NORM.DIST Function5:18
Explore the normal distribution, a bell curve centered at the mean with spread by the standard deviation. Use Excel's norm.dist to convert data to z-scores and visualize standard normal distribution.
Calculating Standard Scores2:55
Calculate z scores in Excel to compare defect rates — west work group A vs east work group M — against population mean using the standard deviation in a normal distribution.
Calculating Normal Percentiles with NORM.DIST2:52
Convert z scores to percentile ranks using norm.dist to interpret how data points relate to the mean and standard deviation.
The Shape of The Curve I; The SKEW Function7:27
Explore how to read data distribution in Excel using the skew function to identify negative and positive skew, compare means, and assess normality.
The Shape of The Curve II; The KURT Function6:37
Explore how the kurt function in Excel measures flatness or peakedness to assess normal distribution, revealing negative kurtosis (flat) and positive kurtosis (peaked) in data.
Determining Confidence Intervals & Confidence Intervals for Normal Distribution4:02
Learn to determine confidence intervals for normal distributions from the sample mean and standard deviation, using alpha to set 95% bounds with the Excel confidence.norm function.
Determining Confidence Intervals & Confidence Intervals for T Distribution1:55
Apply the t distribution for small samples with unknown population standard deviation to build confidence intervals. The example shows a 95% interval between 40.99 and 48.35 minutes.
Hypothesis Testing & Z - Test2:43
Perform a z test in Excel with known standard deviation, compute the p value, and compare to a significance level to accept or reject the null hypothesis, using pizza times.
Hypothesis Testing & T - Test2:24
Learn to conduct t tests for two samples when the population standard deviation is unknown, use one or two-tailed tests in Excel, and apply paired, equal-variance, or unequal-variance options.

Requirements

Microsoft Excel (Office 2021 or Microsoft 365) installed.
Basic knowledge of Excel (e.g., entering data, simple calculations).
Access to a computer with a stable internet connection.
No prior advanced Excel skills required; suitable for beginners.

Description

This course focuses on building and applying statistical formulas in Microsoft Excel, with an emphasis on both descriptive and inferential analysis. It is designed for students, professionals, and data users who want to deepen their understanding of statistics and apply it using the tools built into Excel.

In the first part of the course, students will explore descriptive statistical formulas. They will learn how to count, sort, and summarize data using functions such as COUNT, COUNTIF, and COUNTIFS. The course introduces key methods for calculating averages (mean, median, and mode), as well as techniques to measure variation using range, variance, and standard deviation. Students will also discover how to analyze extremes in data using MAX, MIN, LARGE, and SMALL functions and organize information with frequency distributions.

The second part of the course covers inferential statistics. Learners will work with real-world scenarios involving sampling, probability distributions, and variable relationships. Topics include extracting random and periodic samples, calculating covariance and correlation, and interpreting probability using Excel functions. The course also introduces students to hypothesis testing and confidence intervals, preparing them to make data-driven decisions.

By the end, students will be able to perform a wide range of statistical calculations in Excel, gaining both technical skills and statistical literacy for academic, professional, or analytical tasks.

Let me know if you'd like a shorter version for a flyer or a longer one for a syllabus!

Who this course is for:

This course is for students learning statistics using Excel.
Ideal for business and economics majors working with real-world data.
Great for analysts who need to summarize and interpret datasets.
Designed for anyone performing descriptive and inferential analysis.
Helpful for users calculating averages, ranges, and standard deviation.
Perfect for professionals exploring data relationships and trends.
Made for those conducting sampling and working with probability.
Useful for Excel users applying hypothesis testing and confidence intervals.
A fit for learners needing to build frequency distributions in Excel.
Tailored for anyone using Excel to perform statistical calculations.

Inferential and Descriptive Statistical Formulas in Excel

What you'll learn

Explore related topics

Course content

Building Descriptive Statistical Formulas22 lectures • 41min

Building Inferential Statistical Formulas20 lectures • 1hr 23min

Requirements

Description

Who this course is for: