Channel: ExcelIsFun

Category: Education

Tags: unpivotarray formulasimproper data setspilled array formulasexcel datafilter array functionoutliershighline collegemike girvinbox plotbusn 210box & whisker chartexcel is funexcel statistical analysisbusiness statistical analysisdata analysis5-number summaryquartile.inc functionesa 15countifs functionexcel statisticsproper data setpower querymicrosoft 365 excelmike excelisfun girvinstatistical analysisexcelisfunchapter 3

Description: Download Excel File: excelisfun.net/files/Ch03-ESA.xlsm csv file: excelisfun.net/files/CPAScoreData.csv 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