Excel Statistical Analysis 12: Percentile, Quartile, Percentile Rank and Rank Functions & Algorithms

Learn about calculating percentiles, quartiles, percentile rank and rank. Lean about two different algorithms for the percentile calculation. Learn about how to use eight different Excel built-in functions: ROWS, PERCENTILE.EXC, PERCENTILE.INC, MEDIAN, QUARTILE.EXC, QUARTILE.INC, PERCENTRANK.INC, RANK.AVG
Topics:
1. (00:00) Introduction
2. (00:18) What are Percentiles and Quartiles?
3. (01:30) Using PERCENTILE.EXC and PERCENTILE.INC functions to calculate percentiles
4. (02:38) What the “.EXC” and “.INC” dot extensions mean and what they represent
5. (05:29) Math Algorithm for PERCENTILE.EXC function
6. (05:36) Hide columns
7. (07:49) Math Algorithm for PERCENTILE.INC function
8. (08:24) Remove Number Formatting with General Number Formatting
9. (09:22) Calculating Median with the five different functions: PERCENTILE.EXC, PERCENTILE.INC, MEDIAN, QUARTILE.EXC, QUARTILE.INC
10. (09:35) Unhide columns
11. (10:06) Using the QUARTILE.EXC, QUARTILE.INC functions
12. (10:52) Using the , PERCENTRANK.INC function to calculate the percentile based on a marker in the data set.
13. (11:50) Using RANK.AVE to rank a number
14. (13:02) Creating a Five Number Summary for 490 rows of CPA score data using the functions PERCENTILE.INC and QUARTILE.INC.
15. (13:58) Compare Old School Formula Method and New School Method to calculate the five number summary using the QUARTILE.INC function
16. (05:13) Interpreting Five Number Summary
17. (16:22) Calculate percentile, percentile rank and rank for quarterly sales data for a company.
18. (18:28) Summary
19. (18:54) Conclusion and Video Links

What is difference between PERCENTILE.EXC and PERCENTILE.INC functions?

What is difference between QUARTILE.EXC and QUARTILE.INC functions?

[celia]

Hello Mike, Happy New Year! Thank you for another great video! What a coincidence. have a question regarding this topic and I'll highly appreciate if you could kindly provide some guidance! I'm wondering if there is a way to find the number that appears the most frequently in the bottom 25% percentile of a dataset.

[julienbouillot]

I literally was looking for this topic on your channel on Monday and was sooo disappointed not to find on my favorite excel YouTube Chanel.. 2 days later, there it is.. Thank you!! you made my day!!

[nsanch]

Thank you Mike for the great video on percentiles and quartiles. I'm following along and learning a lot; thank you!

[wayneedmondson]

Awesome Mike! More statistical gold using some great functions. Thanks for the good fun and learning :)) Thumbs up!!

[chrism]

Thank you Mike, this was great, as usual!

[srikanthtr]

Can you please let me know what Text Book you were referring to in your video?

[sohailimran]

Hi mike, I am big fan of your video as it help me a lot..i have query if I want to found out the difference between leaving date and say number, eg if we have leave date for employee and we have the column for how many days in that month then how to get know the number of days he worked in that month. Thank you for your help.

[darrylmorgan]

Boom!Really Great Class..Thank You Mike :)

[Dany-nshg]

Good one, thanks. Do you know the formula to get all the sunday date of January 2022 for eg (2, 9.16, 23 & 30) in different cell?