Understanding Statistical functions in EXCEL 2010+ (Part 1)

Learn how to compute and apply basic excel statistical functions using EXCEL 2010 and 2013.
4.2 (18 ratings) Instead of using a simple lifetime average, Udemy calculates a
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.
391 students enrolled
$50
Take This Course
  • Lectures 62
  • Contents Video: 3 hours
    Other: 7 mins
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 3/2013 English

Course 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.


What are the requirements?

  • Access to EXCEL 2010+
  • Basic notions of introductory statistics or willingness to to take the courses that I teach in the subject matter

What am I going to get from this course?

  • 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

What 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.

What you get with this course?

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.

Curriculum

Section 1: Welcome to Statistical functions in EXCEL 2010 - 2013
01:54
This video explains what to know about Statistical functions in EXCEL 2010 (Part1)
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..
05:57
Learn the basics of EXCEL 2010 main screen and how to insert functions into the main window.
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
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.
03:26
You will learn how to use the functions for computing the sample mean, the median and the mode in this video.
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.
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.
1 question

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.


1 question
01:44
We show how to compute the Mean Average Deviation of a dataset which is a measure of spread of the data.
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.
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 N-1
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.    
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.
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.
2 questions

How do you compute the sample standard deviation?

1 question

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

04:30
The function allows counting the number of data points that fall into a given category, bin, etc...
                                                            
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.
Computing Pearson's coefficient of skewness
1 question
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.
02:09
In this video, we show finding the smallest first, second, third, fourth, etc... element of a dataset with the SMALL function.
01:51
In this video, we show finding the largest first, second, third, fourth, etc... element of a dataset with the LARGE  function.
01:13
We show how to find the sum of the observations with the SUM function
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.
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.
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.
1 question



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.
1 question
You will apply what you learned with the quartiles to detect outliers in data.
02:36
We show how to obtain the rank of a data point in a dataset by using the function RANK.EQ
03:14
We show how to obtain the rank of a data point in a dataset by using the function RANK.AVG
01:40
We show ho to find the sum of squares of the measurements in a dataset.
01:04
We show to find the smallest value or minimum value in a dataset
01:02
We show how to find the largest measurement or maximum of a dataset
Section 3: Analysis using Correlation, regression and forecasting functions
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.
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.
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.
03:07
The trend function allows predicting future values assuming that there exists a linear relationship between the two quantitative variables.
04:16
The forecast function allows predicting future values assuming that there exists a linear relationship between the two quantitative variables.
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 (R-SQUARE) is also put on the equation line.
04:18
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.
03:10
In this video, we show how to compute the regression line with the LINEST function.
Section 4: Using Discrete Probability Distribution functions
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.
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.
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?
Finding the number of permutations
1 question
1 question
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.
03:00
Given a probability distribution table, how do we compute probabilities from the table?  The PROB function shows how to make such computations.
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.
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..


01:56
The faction returns the number of permutations or arrangements of a given number.
01:32
We show how to generate a random number between a range of fixed numbers
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
02:46
In this video, we show how to create a frequency bar graph.
02:06
In this video, we show how to create a pie chart.
07:48
In this video, we use Friedman and Diaconis formula to help determine how many bins to use in a histogram.
05:28
In this video, we show how to construct a histogram using bins that were previously calculated using Friedman and Diaoconis formula.
03:56
In this video, we show how to construct a relative frequency bar graph.
Section 6: New Statistical functions in EXCEL 2013
02:20

About using the NEW statistical functions introduced in EXCEL 2013

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.

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.

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.

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
6 pages
Document explaining in detail how to use the Binomial Distribution.
04:30
How to obtain descriptive statistics with the analysis Toolpak
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
03:51
Thank you for taking the class and next steps.
Computing descriptive statistics of the baseball players data
06:42

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Luc Zio, Adjunct faculty of Math and Statistics

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 hands-on experience with Statistics.

Ready to start learning?
Take This Course