Understanding Statistical functions in EXCEL 2010+ (Part 1)
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice realworld skills and achieve your goals.
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 810 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.
Not for you? No problem.
30 day money back guarantee.
Forever yours.
Lifetime access.
Learn on the go.
Desktop, iOS and Android.
Get rewarded.
Certificate of completion.
Section 1: Welcome to Statistical functions in EXCEL 2010  2013  

Lecture 1  01:54  
This video explains what to know about Statistical functions in EXCEL 2010 (Part1) 

Lecture 2  06:42  
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.. 

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

Lecture 4  01:17  
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. 

Section 2: Computing descriptive Statistical functions in EXCEL 2010  
Lecture 5  04:30  
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. 

Lecture 6  03:26  
You will learn how to use the functions for computing the sample mean, the median and the mode in this video. 

Lecture 7  02:23  
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. 

Lecture 8  01:42  
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. 

Quiz 1  1 question  
Lecture 9  01:52  
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. 

Quiz 2  1 question  
Lecture 10  01:44  
We show how to compute the Mean Average Deviation of a dataset which is a measure of spread of the data. 

Lecture 11  01:39  
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. 

Lecture 12  01:24  
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 N1 

Lecture 13  01:27  
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. 

Lecture 14  01:50  
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. 

Lecture 15  01:43  
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. 

Quiz 3  2 questions  
How do you compute the sample standard deviation? 

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

Lecture 16  04:30  
The function allows counting the number of data points that fall into a given category, bin, etc... 

Lecture 17  01:38  
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. 

Quiz 5 
Computing Pearson's coefficient of skewness

1 question  
Lecture 18  01:50  
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. 

Lecture 19  02:09  
In this video, we show finding the smallest first, second, third, fourth, etc... element of a dataset with the SMALL function. 

Lecture 20  01:51  
In this video, we show finding the largest first, second, third, fourth, etc... element of a dataset with the LARGE function. 

Lecture 21  01:13  
We show how to find the sum of the observations with the SUM function 

Lecture 22  02:01  
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. 

Lecture 23  01:18  
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. 

Lecture 24  01:28  
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. 

Quiz 6  1 question  
Lecture 25  04:12  
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. 

Quiz 7  1 question  
You will apply what you learned with the quartiles to detect outliers in data. 

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

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

Lecture 28  01:40  
We show ho to find the sum of squares of the measurements in a dataset. 

Lecture 29  01:04  
We show to find the smallest value or minimum value in a dataset 

Lecture 30  01:02  
We show how to find the largest measurement or maximum of a dataset 

Section 3: Analysis using Correlation, regression and forecasting functions  
Lecture 31  01:43  
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. 

Lecture 32  02:12  
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. 

Lecture 33  02:50  
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. 

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

Lecture 35  04:16  
The forecast function allows predicting future values assuming that there exists a linear relationship between the two quantitative variables. 

Lecture 36  07:37  
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 (RSQUARE) is also put on the equation line. 

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

Lecture 38  03:10  
In this video, we show how to compute the regression line with the LINEST function. 

Section 4: Using Discrete Probability Distribution functions  
Lecture 39  02:41  
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. 

Quiz 8  2 questions  
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. 

Lecture 40  03:04  
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? 

Quiz 9 
Finding the number of permutations

1 question  
Quiz 10  1 question  
Lecture 41  06:58  
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. 

Lecture 42  03:00  
Given a probability distribution table, how do we compute probabilities from the table? The PROB function shows how to make such computations. 

Lecture 43  03:51  
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. 

Lecture 44  03:02  
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.. 

Lecture 45  01:56  
The faction returns the number of permutations or arrangements of a given number. 

Lecture 46  01:32  
We show how to generate a random number between a range of fixed numbers 

Lecture 47  03:10  
We show how to generate random numbers between zero and one and also show how to generate random numbers from a bell shaped distribution 

Section 5: Graphical display of data  
Lecture 48  02:46  
In this video, we show how to create a frequency bar graph. 

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

Lecture 50  07:48  
In this video, we use Friedman and Diaconis formula to help determine how many bins to use in a histogram. 

Lecture 51  05:28  
In this video, we show how to construct a histogram using bins that were previously calculated using Friedman and Diaoconis formula. 

Lecture 52  03:56  
In this video, we show how to construct a relative frequency bar graph. 

Section 6: New Statistical functions in EXCEL 2013  
Lecture 53  02:20  
About using the NEW statistical functions introduced in EXCEL 2013 

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

Lecture 55  02:45  
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. 

Lecture 56  02:42  
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. 

Lecture 57  08:55  
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. 

Section 7: Final project and EXAM  
Lecture 58  6 pages  
Document explaining in detail how to use the Binomial Distribution. 

Lecture 59  04:30  
How to obtain descriptive statistics with the analysis Toolpak 

Lecture 60  1 page  
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.  
Section 8: Thank you for taking the class and follow up  
Lecture 61  03:51  
Thank you for taking the class and next steps.  
Lecture 62 
Computing descriptive statistics of the baseball players data

06:42 
I have over 18 years of work experience in the field of statistics as an Applied Statistician. For the last twelve years, I have also been teaching undergraduate college level statistics courses at St Petersburg College. As an Applied Statistician, I have developed over the years a strong interest in using EXCEL as a statistical tool with my classes in order to give to the students real world handson experience with Statistics.