Download Excel File: https://excelisfun.net/files/Ch03-ESA.xlsm
Learn about the Measures of Central Location: Mean, Median, Mode and Moving Average. Learn about Skew. See how to use Power Query to import covid 19 data, calculate the 7-day moving average and plot the data on a Line Chart. Other important Excel & Statistics tips.
Topics:
1. (00:00) Introduction
2. (00:59) Files for chapter 3
3. (01:15) Reminder of printable notes
4. (01:34) Statistical Location of a data point against full data set
5. (02:19) Location: Mean. AVERAGE function.
6. (02:49) Location: Median to locate middle of data set. MEDIAN function.
7. (03:55) Location: Percentile 75%. PERCENTILE.EXC function.
8. (05:07) Population and Sample Mean, X Bar and Mew
9. (06:22) Population Parameter and Sample Statistics
10. (07:27) Mean Calculation: How and Why
11. (09:37) Median Calculation: How and Why
12. (13:54) Mode Calculation: How and Why
13. (14:43) PivotTable to calculate Mode for Categorical Data.
14. (15:36) MODE.MULT and MODE.SNGL functions to calculate Mode for Quantitative Data.
15. (16:27) MODE.MULT and COUNTIFS functions to create a Dynamic Spilled Array Report for all quantitative modes.
16. (18:42) Use PivotTable to find all quantitative modes.
17. (19:47) 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.
18. (22:03) 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.
19. (27:00) Examples of skew.
20. (27:59) Three Bonus Mean Calculations
21. (28:10) Bonus #1: Add multiple columns to average function to calcite the text and quiz averages for a class.
22. (29:18) 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.
23. (37:38) Bonus #3: Calculate 6-month moving average for company sales and plot on a Line Chart.
24. (38:52) Summary
25. (40:02) Conclusion and Video Links