Understanding Statistical functions in EXCEL 2010+ (Part 1)

419 students enrolled

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

Learn how to compute and apply basic excel statistical functions using EXCEL 2010 and 2013.

419 students enrolled

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

Requirements

- Access to EXCEL 2010+
- 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.

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.

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.

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?

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

Download EXCEL 2013 DATAFILE for PRACTICE

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

About the Instructor