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

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Dive into advanced Excel techniques for statistical analysis in this comprehensive 26-minute tutorial. Master Power Query for data import and transformation, utilize array formulas to create a 5-Number Summary, and learn to construct Box & Whiskers Charts from both improper and proper data sets. Explore topics including QUARTILE.INC function, calculating means and outliers, and interpreting CPA data set box plots. Gain proficiency in using COUNTIFS, FILTER array functions, and converting single input-output formulas to spilled array formulas. Understand the difference between improper and proper data sets, and learn how to use Power Query's unpivot feature for data transformation. By the end of this tutorial, create professional Box & Whisker Charts and enhance your Excel skills for statistical analysis.

Syllabus

) Introduction to Box & Wisker Chart and Power Query to convert an Improper Data Set to a Proper Data Set.
) Look at csv table of data, 5-Number Summary and Chart button for Box and Wisker Chart.
) What is a Box and Wisker Chart (Box Plot)? How does it show spread in data?.
) Power Query to Import csv file.
) Create 5-Number Summary. Learn a number of different array formula tricks. Learn about COUNTIFS function also..
) QUARTILE.INC function to calculate 5 numbers for 5-Number Summary.
) Calculate Mean for each CPA Test Score Data Set using AVERAGE function.
) Look at where the numbers from 5-Number Summary are in the Box & Whiskers Chart..
) Two methods for calculating Outliers.
) Calculate Outliers for Box & Whisker Chart. Make calculations with Single Input-Output Formulas..
) Use COUNTIFS function to count the number of Outliers. Learn about Comparative Operators in COUNTIFS, SUMIFS and other similar functions..
) Using FILTER Array Function to show the number of outliers for a given data set.
) Learn about the difference between entering a comparative operator into the COUNTIFS and Direct Logical Test formula elements.
) Convert Outlier Make Single Input-Output Formulas to Spilled Array Formulas.
) For this example, with the five column data set, we cannot spill the COUNTIFS formula.
) Create Box & Whisker Chart from table with five columns of CPA Score data.
) Changing Box and Whiskers Chart from Exclusive Quartile Calculation to Inclusive.
) Interpreting the CPA Data sets Box Plots.
) What is an Improper Data Set with a Pivoted Column (Field)? What is a Proper Data Set with only Variable Fields?.
) Unpivot Power Query feature to convert an Improper Data Set to a Proper Data Set..
) Create Box & Whisker Chart from proper data set table with one column for Test Name and one for Test Score.
) Summary.
) Conclusion and Video Links.