Understanding Statistical functions in EXCEL 2010+ (Part 1)
3.5 (22 ratings)
419 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Understanding Statistical functions in EXCEL 2010+ (Part 1) to your Wishlist.

# Understanding Statistical functions in EXCEL 2010+ (Part 1)

Learn how to compute and apply basic excel statistical functions using EXCEL 2010 and 2013.
3.5 (22 ratings)
419 students enrolled
Created by Luc Zio
Last updated 2/2017
English
Current price: \$10 Original price: \$20 Discount: 50% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
• 3 hours on-demand video
• 35 Supplemental Resources
• Access on mobile and TV
• Certificate of Completion
What Will I Learn?
• By the end of this course you will be comfortable in using many fundamental statistical functions in EXCEL 2010 or 2013
• Knowledgeable about constructing histograms, relative frequency and frequency bar graphs
• Able to estimate correlations and make an interpretation of the results.
• Able to fit linear regression equation to data
• Comfortable using the Binomial distribution, Poisson and the Hypergeometric
• Understand how to use random numbers, permutations and combinations
• Knowledgeable about finding outliers in data
View Curriculum
Requirements
• Basic notions of introductory statistics or willingness to to take the courses that I teach in the subject matter
Description

Updated course to include Statistical functions in EXCEL 2013
The course has been updated to include the latest fundamental statistical functions found in EXCEL 2013. With this update, you are given a new set of capabilities to enhance your analytics toolbox using EXCEL.

Understand and solving statistical problems using EXCEL
This course is about learning to leverage the power of EXCEL 2010 and 2013 statistical tools for solving data analysis problems in Business, education and many other areas where analytics are needed.

You will learn how to use many excel statistical functions for obtaining descriptive statistics, correlations, probabilities, histograms, fitting a linear regression, obtaining XY plots, bar graphs, frequencies, percentiles, quartiles, ranking data, etc.

Over 56 videos with detailed explanations using real data to illustrate the concepts.

Course can be completed in 8-10 weeks depending of background, assiduity and interest.

Quizzes and a final project to test your understanding with real world problems

Carefully selected examples in the videos to increase your understanding of each topic.

Take this Excel Statistical course now and have a great understanding of using Excel Statistical Functions for analytics.

Who is the target audience?
• College students taking a statistics course and who are interested in practical data analysis or knowing EXCEL for analyzing statistical data
• Anyone interested in data analytics using EXCEL as the main statistical tool for analysis
• Data analysts, programmers and other professionals who want to understand EXCEL statistical capabilities
• Students and anyone interested in learning new skills.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
62 Lectures
03:13:06
+
Welcome to Statistical functions in EXCEL 2010 - 2013
4 Lectures 15:50
This video explains what to know about Statistical functions in EXCEL 2010 (Part1)
Preview 01:54

We discuss the statistical functions that will be covered in this course which encompasses all descriptive statistical functions, probability related functions, correlation and regression, as well as many other functions such as the histogram, ranking, etc..
Preview 06:42

Learn the basics of EXCEL 2010 main screen and how to insert functions into the main window.
Preview 05:57

In this video, we show how to enable the Analysis ToolPak which will then allow the user to tap into advances statistical libraries in EXCEL 2010.
Enabling the Analysis ToolPak in EXCEL 2010
01:17
+
Computing descriptive Statistical functions in EXCEL 2010
26 Lectures 55:26
This video explains how to obtain the following descriptive statistics with the Analysis ToolPak: Mean, Mode, Median, Variance, Standard deviation, Coefficient of skewness, Range, Maximum and Minimum.
Using the Analysis ToolPak Descriptive Statistics Library
04:30

You will learn how to use the functions for computing the sample mean, the median and the mode in this video.
Measures of central tendency of the data: Mean, median and mode
03:26

In this lecture, we show how to use to calculate the trimmed mean of sample data by specifying a certain percentage of the observations to be trimmed.
Obtaining the Trimmed mean of the data
02:23

We show how to compute the harmonic mean of the data which is a mean used for averaging rates. Compared with the arithmetic mean, it tends to reduce the influence of large measurements (outliers) while giving weight to the smaller ones.
If the data has extreme outliers, the harmonic mean is preferable to the arithmetic mean.
Obtaining the Harmonic mean of the data
01:42

Finding average rate of speed with the harmonic mean
1 question

We show how to compute the geometric mean of the data, which is a mean commonly used for averaging interests rates or investments returns. It is also used for  proportional growth cases or any growth related data such as cases where we compute the annual compound growth rate.

Obtaining the Geometric mean of the data
01:52

Finding the average rate of growth with GEOMEAN
1 question

We show how to compute the Mean Average Deviation of a dataset which is a measure of spread of the data.
Computing the Mean Average Deviation (MAD)
01:44

In this video, we are computing the sum of square deviations of the data from their mean.  The sample variance is equal to the Sum of squares deviations divided by the sample size minus one.
Computing the sum of squares deviations from the mean
01:39

In this video, we are computing the sample variance which is a measure of dispersion or spread of the data.  It measures how much the data vary.  For example, if a student scores are:  85, 86, 85, 84, 85  the variance will be almost close to zero because the data do not vary much. Conversely, if a students scores are:  10, 67, 25,  82, 55  the variance will be much larger because the grades vary a whole lot more.   For the sample variance, we divide the Sum of Squares deviations by N-1
Computing the sample variance of the data
01:24

In this video, we are computing the population variance which is a measure of dispersion or spread of the data for the entire population.  For the population variance, we divide the Sum of Squares deviations by N.
Computing the population variance of the data
01:27

In this video, we compute the sample standard deviation which is the square root of the sample variance. The standard deviation is used for the interpretation of the spread or dispersion because it is as the same unit as the data. For example, if we have the weights of babies at birth, their variance will be in pounds squared.  Since we want to talk about the spread or dispersion of their weights in pounds, we will take the square root of the variance (standard deviation) to obtain the spread in pounds.
Sample standard deviation of the data
01:50

In this video, we compute the population standard deviation which is the square root of the population variance.  Since we want to talk about the spread or dispersion of the data at the same unit as the one the data was measured in (pounds, meters, inches, etc....) we will take the square root of the variance (standard deviation) to obtain the spread in pounds.
Population standard deviation of the data
01:43

How do you compute the sample standard deviation?

Computing a standard deviation
2 questions

This quiz will test your understanding about using the Analysis Tool Pack to obtain descriptive statistics.

Computing Descriptive Statistics using EXCEL Analysis ToolPak library
1 question

The function allows counting the number of data points that fall into a given category, bin, etc...

Grouping the data with the Frequency function
04:30

We are Computing the coefficient of skewness which measures how skewed is the data. If the skewness is zero, the data is not skewed.  If it is significantly greater than zero, then the data will be right skewed or positively skewed.  If it is significantly less than zero, then the data will be left skewed or negatively skewed.
Computing the coefficient of skewness of the data
01:38

Computing Pearson's coefficient of skewness
1 question

The word if from the Greek word kurtos, which means curved, arching. We are calculating the kurtosis of the data which measures how heavy is the tail of the data. When the kurtosis is high, the distribution has a sharper peak and longer, fatter tails.  Conversely, when the kurtosis is low, the distribution has a more rounded peak and shorter, thinner tails.
Calculating the Kurtosis of a sample data
01:50

In this video, we show finding the smallest first, second, third, fourth, etc... element of a dataset with the SMALL function.
Obtaining the kth smallest measurement of the data where k=1,2,...n
02:09

In this video, we show finding the largest first, second, third, fourth, etc... element of a dataset with the LARGE  function.
Obtaining the kth largest measurement of the data where K=1,2,..
01:51

We show how to find the sum of the observations with the SUM function
Obtaining the sum of the measurements in a dataset
01:13

We show how to obtain percentiles of the data using percentile.INC. The function will calculate the Kth percentiles where k is in the range of 0 through  1 with zero and one included.  For example, if we use the function and replace K by zero, we will get the smallest data point in the measurements.  If we replace K by 1, we will get the maximum or largest data point.
Obtaining percentiles with Percentiles.INC
02:01

We show how to obtain percentiles of the data using percentile.EXC. The function will calculate the Kth percentiles where k is in the range of 0 through  1 with zero and one excluded.  For example, if we use the function and replace K by zero, we will get an error..  If we replace K by 1, we will also get an error.
Obtaining percentiles with Percentiles.EXC
01:18

In this video, we show how to obtain percentile rank of a measurement, which calculates the rank of a measurement in a dataset as a percentage with (0 and 1) inclusive.
Calculating the Percentile Rank of an observation
01:28

Finding percentiles rank
1 question

In this video, we show how to obtain the quartiles which are measures of relative standing. The zero quartile is the minimum of the data, the first quartile is the 25th percentile, the second quartile is 50th percentile or median, the third quartile is the 75th percentile, the fourth quartile is maximum of the data.
Obtaining quartiles with Quartiles.EXC
04:12

You will apply what you learned with the quartiles to detect outliers in data.
Application of Quartiles in data screening
1 question

We show how to obtain the rank of a data point in a dataset by using the function RANK.EQ
Ranking data using Rank.EQ function
02:36

We show how to obtain the rank of a data point in a dataset by using the function RANK.AVG
Ranking data using Rank.AVG function
03:14

We show ho to find the sum of squares of the measurements in a dataset.
Obtaining the Sum of Squares of Data
01:40

We show to find the smallest value or minimum value in a dataset
Obtaining the Minimum with the Min function
01:04

We show how to find the largest measurement or maximum of a dataset
Obtaining the Maximum with the Max function
01:02
+
Analysis using Correlation, regression and forecasting functions
8 Lectures 29:13
The Pearson's correlation coefficient calculates the correlation between two quantitative variables. When it is zero, there is no linear association between the two variables. When it is close to 1.0,  the two variables are said to be strongly positively correlated. When it is close to -1.0,  the two variables are said to be strongly negatively correlated.
Using Pearson's correlation coefficient function
01:43

The function calculates the covariance between two quantitative variables. When the covariance is zero, the correlation will also be zero, when it is positive, the correlation will be positive and when it is negative, the correlation will be negative.
Calculating the sample covariance between two quantitative variables
02:12

The sample correlation coefficient calculates the correlation between two quantitative variables. When it is zero, there is no linear association between the two variables. When it is close to 1.0,  the two variables are said to be strongly positively correlated. When it is close to -1.0,  the two variables are said to be strongly negatively correlated.
Computing the sample correlation coefficient with CORREL
02:50

The trend function allows predicting future values assuming that there exists a linear relationship between the two quantitative variables.
Forecasting data using the trend function
03:07

The forecast function allows predicting future values assuming that there exists a linear relationship between the two quantitative variables.
Forecasting future data with the Forecast function
04:16

In this video,we plot  two quantitative variables  and then fit a linear regression equation to the data.  A measure of the variance explained by the explanatory variable (R-SQUARE) is also put on the equation line.
Fitting a linear regression to data using XY Plot with options
07:37

In this video, we are calculating the regression model R-SQUARE or coefficient of determination which measures the amount of the variance explained by the explanatory variables. In general, the higher the R-SQUARE (at least 80%) the better the model fit.
Obtaining RSQUARE for the linear regression
04:18

In this video, we show how to compute the regression line with the LINEST function.
Fitting the linear regession with LINEST function.
03:10
+
Using Discrete Probability Distribution functions
9 Lectures 29:14
In this video, we are calculating the number of combinations of a smaller subset of elements (r) among a lager set (n).
For example, how many ways can we select a hand (group of five cards) in a deck of 52 cards?  That will be the number of combinations of  5 cards among 52.
Computing the number of combinations of r elements among n elements
02:41

In how many ways can we select a group of 3 students in a class of 40 students.  Hint: This is found
by calculating the number of combinations of 3 students among 40. Use the COMBIN function.
Using combinations in practice
2 questions

In this video, we are calculating the number of permutations of a smaller subset of elements (r) among a lager set (n).
For example, how many ways can we select a gold winner,  bronze and silver in a group of ten countries participating at a tournament?
Using the Permutation function
03:04

Finding the number of permutations
1 question

Using permutations to solve problems
1 question

In this video, we show how to effectively use the Binomial distribution and compute probabilities arising from it. The Binomial  is used in the case where we have dichotomous outcomes (Example: Smoke / Do not smoke), independent trials, fixed probability of success, fixed number of trials.
Computing Binomial Probability Distribution Probabilities
06:58

Given a probability distribution table, how do we compute probabilities from the table?  The PROB function shows how to make such computations.
Using the Probability function for discrete probability distributions tables
03:00

In this video, we show how to calculate probabilities using the Hypergeometric distribution a discrete distribution in statistic.  A problem is provided to fully explain and illustrate the use of the function.
Calculating Hypergeometric Distribution Probabilities
03:51

In this video, we show how to compute probabilities of the Poisson distribution that is used commonly to model the number of events occurring within a given time interval.   Examples: The number of customers arriving at a pizza parlor, bank counter, the number of cars arriving at a traffic light, etc..

Calculating Poisson Distribution Probabilities
03:02

The faction returns the number of permutations or arrangements of a given number.
Using the Factorial function to obtain the number of permutations of elements
01:56

We show how to generate a random number between a range of fixed numbers
Obtaining a random number in a fixed interval
01:32

We show how to generate random numbers between zero and one  and also show how to generate random numbers from a bell shaped distribution
Generating Random numbers
03:10
+
Graphical display of data
5 Lectures 22:04
In this video, we show how to create a frequency bar graph.
Creating a frequency bar graph
02:46

In this video, we show how to create a pie chart.
Creating a basic pie chart
02:06

In this video, we use Friedman and Diaconis formula to help determine how many bins to use in a histogram.
Using Friedman and Diaconis technique to determine an optimal number of BINS
07:48

In this video, we show how to construct a histogram using bins that were previously calculated using Friedman and Diaoconis formula.
Creating a Histogram using some pre-determined BINS
05:28

In this video, we show how to construct a relative frequency bar graph.
Creating a relative frequency bar graph
03:56
+
New Statistical functions in EXCEL 2013
5 Lectures 19:16

About using the NEW statistical functions introduced in EXCEL 2013

02:20

In this lecture, you will learn about SKEW.P a new function introduced in EXCEL 2013 to compute the skewness coefficient of a population.

Using EXCEL 2013 SKEW.P function
02:34

In this lecture, you will learn about PERMUTATIONA a new function introduced in EXCEL 2013 to compute the number of permutations with repetitions. You will understand in practice what is a permutation with repetitions.

Using EXCEL 2013 PERMUTATIONA function to compute permutations with repetitions
02:45

In this lecture, you will learn about COMBINA a new function introduced in EXCEL 2013 to compute the number of combinations with repetitions. You will understand in practice what is a combination with repetitions.

Using EXCEL 2013 COMBINA function to compute combinations with repetitions
02:42

After this lecture, you will have a mastery of the capabilities of the new function BINOM.DIST.RANGE and will be able to use them to effectively compute Binomial probabilities.

Understanding EXCEL 2013 BINOM.DIST.RANGE function in practice
08:55
+
Final project and EXAM
3 Lectures 04:30
Document explaining in detail how to use the Binomial Distribution.
Applying the concepts to real world examples
6 pages

How to obtain descriptive statistics with the analysis Toolpak
Obtaining Descriptive Statistics with the Analysis ToolPak
04:30

You will find a zipped file containing Blood fat data and the project document. Please take the project to test your understanding of the concepts.
Important files for practice and final exam material
1 page
+
Thank you for taking the class and follow up
2 Lectures 10:33
Thank you for taking the class and next steps.
Thank you and next steps about the class: Statistical Functions (Part 2)
03:51

Computing descriptive statistics of the baseball players data
06:42