What you'll learn

Analyze data using advanced statistical functions and Excel's Data Analysis Toolpak for summary statistics.
After completing this course, you will feel comfortable using EXCEL advanced statistical functions for Analytics and Real World cases.
Conduct t-tests, ANOVA, and chi-square tests for categorical data analysis.
Advanced statistical functions about the Normal or Bell-Shaped distribution, Student t distribution, chi square, lognormal, negative binomial, etc..
Solve inferential statistical problems using the functions and libraries you studied in the course

Course content

9 sections • 44 lectures • 2h 32m total length

What this class is about3:51
Introduction about the class and me
How to insert functions into EXCEL 2010 main window5:57
Learning the main window interface and inserting functions

Finding the standard normal distribution probabilities6:27
In this video, we are computing standard normal distributions probabilities. This is equivalent to reading the Standard Normal Z table for finding probabilities, except that here, EXCEL computes it directly for us.
Normal Distribution NORM.DIST function7:09
In this video, we learn how to compute probabilities about a Normal distribution with a given mean and standard deviation.
Finding the inverse of a normal distribution4:55
In this lesson, we learn how to solve problems related to the inverse of the stand normal distribution. For example, if the weights of pygmy goats are normal with a mean of 80 pounds and standard deviation of 3.5 pounds, what goat weight corresponds to the 95th percentile?
Find the inverse (Z-value) of the standard normal distribution3:01
In this lesson, we are calculating inverse or Z-value of a standard normal distribution. For example, what is the Z value corresponding to the 90th percentile of the standard normal distribution?
How to plot the standard normal distribution curve7:35
In this lesson, we use the standard normal density function to create a bell-shaped curve. This curve comes in handy if you want to show how the bell-shaped distribution looks like.
Creating a standard normal distribution table7:19
In this video, we use built-in standard normal distribution functions to create a standard normal table. The table can be printed and used in lieu of the tables in statistical books.
How to standardize by computing the Z value1:39
This video shows how to compute the Z values or standardize the data in EXCEL 2010
Using a Z-test to test an hypothesis about the population mean4:39
In this lesson we show how to use a Z-test function to test an hypothesis about the population mean assuming a normal distribution.
Computing the margin of error for a large sample size confidence interval about the population mean3:24
In this video, we show that the function CONFIDENCE.NORM computes the margin of error for a large sample size confidence interval about the population mean assuming a normal distribution.

Obtaining a left-tailed P-value about the population mean with T.DIST2:54
Finding the left tailed P-value for testing an hypothesis about a population mean using Student T.DIST function
Finding the critical table value corresponding to a left-tailed student t distribution2:19
In this lesson, we show how to find a critical Student t value corresponding to a left tailed test of hypothesis about the population mean
Finding the critical table value corresponding to a two-tailed t distribution2:26
In this video, we show how to find the critical value corresponding to a two tailed test using Student t test
Finding the right-tailed P-value using for testing a population mean using Student t distribution3:17
In this video, we show how to find a right-tailed P-value for a Student t test.
Finding a two-tailed P-value when testing an hypothesis about the mean using Student t distribution2:11
Finding a two-tailed P-value when testing an hypothesis about the population mean using Student t distribution
Creating a Student t table with a T.INV function5:36
In this video, we show How to create a Student t table using EXCEL Student t function
Testing hypothesis about two populations means using Student t-test3:33
In this lesson, we show how to test an hypothesis about two populations means using Student t test. We provide detailed explanation for the test.
Computing the margin of error for estimating a population mean using Student t distribution4:57
In this video, we are calculating the Margin of Error corresponding to a small sample size confidence interval for the population mean using Student t distribution.

Finding the left-tailed P-value for testing an hypothesis about the population variance using chi.dist3:09
In this lesson, we show how to find the left tailed P-value of a chi square distribution. This is important when we are testing an hypothesis with a left tailed test.
Finding the right-tailed P-value for testing an hypothesis about a population variance using the Ch square distribution2:30
In this lesson, we show how to find the right tailed P-value of a chi square distribution which is used to decide about statistical hypotheses.
Finding the critical table value for testing a left-tailed hypothesis about a population variance using Chi Square distribution3:16
In this video, we are finding the critical tables values corresponding to a chi square left tailed test
Find the critical table value for testing a right-tailed hypothesis about a population variance using the Chi square distribution2:27
In this lesson, we show how to find the critical tables values corresponding to a chi square right tailed test
Testing an hypothesis of independence using the Chi square test5:17
In this lesson, we show how to conducte a test of chi square test of independence using the chi square test

Computing a left-tailed P-value for testing the equality of two population variances using the F distribution3:06
In this lesson, we show how to find the left tailed P-value with F.DIST function
Computing a right-tailed P-value for testing the equality of two populations variances using the F-distribution3:17
In this video, we show how to find the right tailed P-value of the F distribution
Finding a right-tailed critical value for testing two variances using the F.INV.RT4:08
In this lesson, we show how to find the right tailed critical value of an F distribution
Testing equality of two populations variances with the F-test1:45
In this lesson, we show how to test that two populations variances are equal with the FTEST function. This step is necessary for those who want to test the equality of two populations means.

Computing Exponential distribution probabilities2:04
In this lesson, we show how to use the exponential distribution in practice. The exponential distribution is used to model waiting time probabilities.. Example: How long will we have to wait until a customer arrives at a toll booth?How long will machinery work without needing repair?
Fitting exponential growth models to data with the growth function5:25
In this video, we show how to fit the exponential growth model to data that appears to have an expontential growth and then make future forecasts.
Evaluating Gamma distribution probabilities2:30
We show in this video how to use the Gamma distribution, another waiting time model to compute probabilities. It is used in marketing, insurance risk, economics, meteorology, etc..
Computing factorials with the Gamma related functions3:49
In this video we show how to use the GammaLN function to compute factorials of numbers
Computing probabilities of the Negative binomial distribution3:34
In this video, we show how to compute probabilities about the negative binomial for solving a real world problem. It returns the probability that there will be a given number of failures before the rth success. The geometric distribution is a special case of of the Negative Binomial. It is defined as the num.ber of trials (failures) until the 1st success
Using the Log normal distribution function to compute probabilities2:26
In this video, we show how to use the lognormal distribution, which is a distribution where the ln(x) is normally distributed. It is sometimes called the Cobb–Douglas function in business.
Finding the inverse of Log normal distribution3:08
In this video, we show how to compute the inverse of the log normal distribution which is a distribution where the ln(x) is normally distributed. It is sometimes called the Cobb–Douglas function in business.
Using the Weibull distribution to compute probabilities2:19
In this video, we show how to compute probabilities of the Weibull distribution. It is extensively used in the field of reliability engineering for measuring component lifetime.

Advanced new statistical functions in EXCEL 20132:20
In the lecture we talk about the new advanced statistical functions introduced in EXCEL 2013
Using the new Gamma function in EXCEL 20132:18
This lecture explains the use of the newly introduced function Gamma function in EXCEL 2013 to evaluate the gamma of the numbers that are greater or equal to one.
Using the new Gauss function in EXCEL 20134:13
In this lecture, we illustrate how to use the Gauss function to evaluate standard normal distribution probabilities.
Using the PHI function in EXCEL 20133:59
This lecture explains how to use PHI function in EXCEL 2013 to evaluate the density of the standard normal distribution.

Project and practice files8:00
Exercises with solutions to reinforce the understanding of the concepts
Solving problems using the normal distribution (Part1)
Find probabilities under the standard normal distribution
Finding the left tailed P-value for a Student t distribution
Finding the right-tailed P-value for a Student t distribution
Finding the two tailed P-value for a Student t distribution
Practice using the inverse of the standard normal distribution3:00
Finding the Z values of the standard normal distribution using (alpha over 2)
Finding the inverse of the Student t distribution3:00
Finding the Student t values used in confidence interval for a given alpha and sample size n

Requirements

Access to Microsoft Excel (Microsoft 365)
Openness to learning about advanced Excel tools and functions for complex statistical analysis.
Understanding of Fundamental Statistics concepts and basic hypothesis testing.

Description

This course provides an in-depth introduction to advanced statistical functions in Excel, utilizing the latest features and tools available in Excel for Office 365. It is designed for data analysts, students, and professionals who wish to elevate their data analysis skills with advanced Excel capabilities.

In this course, you will learn to harness a variety of powerful statistical functions, including Normal Distribution, Student-t Distribution, Chi-Square Distribution, F Distribution, Gamma, Log-Normal, Negative Binomial, and Exponential Distributions. You’ll also learn to calculate margins of error and construct confidence intervals, crucial skills for conducting precise data analysis. Additionally, the course covers the creation of Standard Normal tables and Student-t tables, important tools for understanding statistical data.

The material is updated with practical examples, quizzes, and Excel files that illustrate each function and its real-world applications. These hands-on activities are designed to help you grasp the concepts quickly and apply them to both educational and professional settings. Detailed screenshots and interactive exercises ensure that you can follow along and effectively learn each function as it is introduced.

By the end of the course, you will have developed a robust toolkit of advanced analytical tools to tackle a wide range of data analysis challenges. Whether you're working on academic projects, business analytics, or professional research, this course will provide you with the skills to analyze data with confidence. Depending on your prior experience, you can complete this course in a month or less.

This course will be invaluable for anyone looking to advance their expertise in statistical analysis and apply these techniques in real-world situations using Excel for Office 365.

Who this course is for:

College students taking statistics classes and interested in using EXCEL advanced statistical functions for their projects
IT specialists and Data Engineers integrating statistical analysis into data pipelines and systems.
Experts analyzing consumer data, market trends, and campaign performance metrics.
Professionals analyzing and interpreting data to provide actionable insights.

What you'll learn

Explore related topics

Course content

Welcome to Mastering Advanced Statistical Functions in Excel2 lectures • 10min

Functions related to the normal or bell-shaped distribution9 lectures • 46min

Functions related to the Student t distribution8 lectures • 27min

Computing Chi-square distribution probabilities and critical values5 lectures • 17min

Computing F-Distribution probabilities4 lectures • 12min

Other advanced Statistical functions in EXCEL 20108 lectures • 25min

Advanced Statistical functions in EXCEL 20134 lectures • 13min

Practice project files and Quizzes3 lectures • 14min

Closing remarks and follow up1 lecture • 2min

Requirements

Description

Who this course is for: