Excel Statistical Analysis - Location, Mean, Median, Mode, Moving Average, Skew & More

Overview

Dive into a comprehensive 40-minute video tutorial on Excel statistical analysis, focusing on measures of central location and data distribution. Master the use of Mean, Median, Mode, and Moving Average calculations using Excel functions like AVERAGE, MEDIAN, and PERCENTILE.EXC. Explore the concepts of population parameters and sample statistics, and learn how to calculate and interpret skew in data sets. Gain practical skills in using PivotTables, dynamic spilled array formulas, and Power Query for data analysis and visualization. Apply your knowledge to real-world scenarios, including COVID-19 data analysis and company sales forecasting. Download the accompanying Excel file to follow along and reinforce your learning through hands-on practice.

Syllabus

) Introduction.
) Files for chapter 3.
) Reminder of printable notes.
) Statistical Location of a data point against full data set.
Location: Mean. AVERAGE function..
) Location: Median to locate middle of data set. MEDIAN function..
) Location: Percentile 75%. PERCENTILE.EXC function..
) Population and Sample Mean, X Bar and Mew.
) Population Parameter and Sample Statistics.
) Mean Calculation: How and Why.
) Median Calculation: How and Why.
) Mode Calculation: How and Why.
) PivotTable to calculate Mode for Categorical Data..
) MODE.MULT and MODE.SNGL functions to calculate Mode for Quantitative Data..
) MODE.MULT and COUNTIFS functions to create a Dynamic Spilled Array Report for all quantitative modes..
) Use PivotTable to find all quantitative modes..
) SORT, UNIQUE and COUNTIFS functions to create a Dynamic Spilled Array Report for categorical data to find the modes. This is really a Frequency Distribution without the total row..
) Skew in Histograms and a Statistical Formula. See the SKEW function. Learn about relationship between Mean, Median and Mode and how they relate to skew..
) Examples of skew..
) Three Bonus Mean Calculations.
) Bonus #1: Add multiple columns to average function to calcite the text and quiz averages for a class..
) Bonus #2: Use Power Query to Import CSV data from a covid government site, calculate 7-day moving average for number of covid 19 cases and plot it on a Line Chart..
) Bonus #3: Calculate 6-month moving average for company sales and plot on a Line Chart..
) Summary.
) Conclusion and Video Links.