Excel Statistical Analysis 15: 5-Number Summary / Box & Whiskers Chart: Power Query & Array Formulas

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch03-ESA.xlsm
Learn about how to use Power Query to import and transform data, use array formulas to create a 5-Number Summary, and who to create a Box & Whiskers Chart from a 5-column Improper Data Set and a Proper Data Set (easier option).
Topics:
1. (00:00) Introduction to Box & Wisker Chart and Power Query to convert an Improper Data Set to a Proper Data Set
2. (01:00) Look at csv table of data, 5-Number Summary and Chart button for Box and Wisker Chart
3. (01:33) What is a Box and Wisker Chart (Box Plot)? How does it show spread in data?
4. (02:23) Power Query to Import csv file
5. (04:15) Create 5-Number Summary. Learn a number of different array formula tricks. Learn about COUNTIFS function also.
6. (06:14) QUARTILE.INC function to calculate 5 numbers for 5-Number Summary
7. (07:47) Calculate Mean for each CPA Test Score Data Set using AVERAGE function
8. (08:11) Look at where the numbers from 5-Number Summary are in the Box & Whiskers Chart.
9. (08:35) Two methods for calculating Outliers
10. (09:15) Calculate Outliers for Box & Whisker Chart. Make calculations with Single Input-Output Formulas.
11. (10:09) Use COUNTIFS function to count the number of Outliers. Learn about Comparative Operators in COUNTIFS, SUMIFS and other similar functions.
12. (11:10) Using FILTER Array Function to show the number of outliers for a given data set
13. (12:19) Learn about the difference between entering a comparative operator into the COUNTIFS and Direct Logical Test formula elements
14. (13:07) Convert Outlier Make Single Input-Output Formulas to Spilled Array Formulas
15. (15:13) For this example, with the five column data set, we cannot spill the COUNTIFS formula
16. (15:56) Create Box & Whisker Chart from table with five columns of CPA Score data
17. (17:22) Changing Box and Whiskers Chart from Exclusive Quartile Calculation to Inclusive
18. (18:44) Interpreting the CPA Data sets Box Plots
19. (19:46) What is an Improper Data Set with a Pivoted Column (Field)? What is a Proper Data Set with only Variable Fields?
20. (21:27) Unpivot Power Query feature to convert an Improper Data Set to a Proper Data Set.
21. (
22. (24:44) Create Box & Whisker Chart from proper data set table with one column for Test Name and one for Test Score
23. (25:38) Summary
24. (26:03) Conclusion and Video Links

ExcelIsFunFri, January 21, 2022 11:17pm