Get 20+ Excel courses at Simon Sez IT, including training for Excel 365, 2021, 2019, 2016, 2013, and more ️ https://www.simonsezit.com/course-category/excel/ Get a FREE 2.4 hour Excel 2019 Beginners course here ►
https://simonsezit.lpages.co/excel-2019-beginner-course-ssit/

In this video, learn how to use the percentile function in Microsoft Excel 2013.

Get the full course on Excel 2013 course here: http://www.simonsezit.com/courses/microsoft/learn-microsoft-excel-2013-advanced-training-tutorials/

Watch the Excel 2013 playlist: https://www.youtube.comhttp://www.youtube.com/playlist?list=PLzj7TwUeMQ3jVfCjoptJb_mimawnVezqh

Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel -)

Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we looked at averages for data using the average and average if function in Excel 2013. In this section we’re going to start by looking at percentiles.
These changed in Excel 2010. If you’ve been using an older version of Excel you probably used the percentile function, and very often people look at 25 percentiles, 50 percentiles, 75 percentiles and use these of ways of describing data sets.

The straightforward use of the percentile function, the legacy percentile function if you like is still available in Excel 2013. Basically if you say percentile, define the data set, so in our case that will be our named Policy Sales Orlando data set, and then you define as a fraction in the range zero to one the percentile that you’re interested in. So let’s suppose you wanted the 25th percentile. You’d put in there 0.25 and what that tells you is the value below which 25% of the data occur. So in our particular case, for the whole four year data set the 25th percentile is 86.

Now in Excel 2013 you are discouraged from using the percentile function and the function to use which has replaced the percentile function is either PERCENTILE.INC or PERCENTILE.EXC. Now largely speaking these two functions produce the same results but there is an important subtle difference between them. PERCENTILE.INC is an inclusive function whereby for any value of K in the range zero to one you will get a result. PERCENTILE.EXC actually throws an error if you use a value of K which is outside the valid range for the data set that you’re working with. Now this is actually a little bit tricky to explain but I’ll try to do so.

Let’s suppose that you’ve got a data set with just ten items of data in it. If you put them in order, then the first data item will effectively be your tenth percentile because it will mean that 10% of your data, one out of ten, is equal to or below that value. If you try to find a five percentile, it’s a bit of a ridiculous question really because you’ve only got ten data items so you can’t find a value below which 5% of your data exists.

Now in that particular case PERCENTILE.INC will just do its best to find you a value, PERCENTILE.EXC will give you an error. The difference is really summarized in this little statement down here about PERCENTILE.EXC. PERCENTILE.EXC will interpolate when the value for the specified percentile lies between two values in the array. If it cannot interpolate for the percentile case specified Excel will return an error.

So what I’ve done here is to replace the old percentile function here with PERCENTILE.INC. I’ve also actually put in 50th percentile which is the median. Note again the PERCENTILE.INC function and the 75th percentile.

Now as I mentioned earlier there are other functions in Excel that have been superseded by newer versions. When you’re doing things like percent rank, there’s now a pair of functions: PERCENTRANK.EXC and PERCENTRANK.INC. There are quartile functions that now represented by QUARTILE.EXC, QUARTILE.INC’s. And then other things such as ranking which have always been a little bit of a problem in Excel where you have equal scores have been replaced by RANK.AVG and RANK.EQ functions. Well worth you reading up on these if you use the statistical functions in Excel.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.