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

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

[bamakaze]

Mike, you are so underrated! Years from now, many people will reflect on their life choices and wish they had watched more of your videos!

[RogerStocker]

Mike "Entertrainer" Girvin
After years still available for amazing entertaining lessons. Thank you so much for sharing huge knowledge in YouTube.

[shubhampawar]

You are the best teacher in my life ❤️💯..Mr statistician ❤️

[chrism]

The Master strikes again. Another great video Mike!

[wayneedmondson]

Great one Mike! Catching up on a few I missed when released. Fun with Box and Whisker charts, 5-number summary and Power Query magic! Thumbs up!!

[canirmalchoudhary]

Unpivot, COUNTIFS, spill, filter so many and on top of above quartile calculations. So great learning.

[Anthony_Lecoq]

Actually last week I was trying to implement a Violin chart in power bi, . I wanted to show the bipolarity betweem big project order sales and small spare parts sales. The data set has several milions rows. It did not well as violin is only based on samples. This video made me realised that this is exactly what I need., I just need to set charactherstic of the order and set it as characteristic of this 5 numbers chart. And bam it is beautifull. Thanks mike for all your input.

[johnborg]

Thanks Mike. Box Plots!!! I never used them. I shall treasure this video. :) :)

[darrylmorgan]

Boom!Really Great Fun Class, Awesome Tips And Tricks...Thank You Mike :)

[kiwikiow]

Good detailed explanation of box & whiskers chart. Thank you Mike. I never knew we can spill ghost formula to the side 👻➡️

[MalinaC]

Thank you for the video! Thumbs up as always :) 👍

[Al-Ahdal]

January 21, 2022 | Version 2202 (Build 14912.20000)


Excel

Improved Power BI-connected PivotTables

Many users have requested improvements for PivotTables connected to Power BI datasets, enabling easy drag-and-drop aggregations by dragging fields into the Values area of the PivotTable. Now they’re here! This work builds on recent improvements that make it easier to create Power BI-connected PivotTables without having to leave Excel.

[joeltochukwu]

Will take me some time to wrap my head around the concepts here. Will have to go through the video again or can you please point to additional resources?

[ExcelInstructor]

Mike Thank you :)
great video as always :)
Knowladge is power.

[roywilson]

Very nice video as always, thanks for publishing it to Youtube. The link to the CSV file seems to be missing from the description, though I was able to grab it by overtyping the spreadsheet file name.
I can't imagine why anyone would use the imported dataset as is when it only takes a few extra clicks to transform it into a proper data set, especially as the set has to be imported via power query. This wonderful tool should be displayed prominently on the Home Tab along with the Manage Data Model button that way maybe more users would research it's uses.

[mattschoular]

Thanks Mike. I see you have been putting out some very extensive videos of late. I hope to get caught up shortly. I am sure I am missing out on some great content.

[ogwalfrancis]

Awesome video, thanks Mike, help with more examples of where to put compare operation eg < in double quotes and where you can directly use it without double quote

[eqggkte]

With regard to the Box & Whisker chart, if you note that the “Lower Outlier Hurdle” and the “Upper Outlier Hurdle” are actual data values, it appears that Excel is setting the “Lower Outlier Hurdle” to the largest data value above the first lower outlier and setting the “Upper Outlier Hurdle” to the smallest data value below the first upper outlier. (I think I said that right.)

[mrahul]

Revision on Unpivot Table Formula we can use TOCAL, for CH03 sheet "5NSBP (an)" Test Name> =TOCOL(IFNA(B20:F20, B21:B403)) and for CPA Score > =TOCOL(B21:F403)

[BaniMoniah]

A question about Filter function, how to filter a table when sum of a column reaches a specific target?