Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
The PERCENTILE Function
The Purpose of the PERCENTILE Function
The PERCENTILE function takes data from a range and returns the k-th percentile. This function allows you to select a range and what percentile you want returned. Examples of percentiles returned would the 90th, 50th, or even 10th percentile of a range of numbers.
Just for a refresh on what a statistical percentile is, if you tested in the 25th percentile of your class, this would mean that 25% of your class tested lower than you did.
How the Percentile Function Works
When inputted the PERCENTILE function must be entered like a formula. To add this function you would complete the following steps:
- Click in the cell where the result is to display.
- Type "=PERCENTILE(". Without the quotations marks of course!
- The array of numbers is chosen followed by a comma. This is the range of numbers that you are finding the percentile for.
- The percentile that needs to be returned followed by a closed parenthesis. This number should be entered in the form of a decimal.
- Lastly, the data need to be entered into the cell.
The syntax for the PERCENTILE function can be seen in more detail here:
There are two arguments in this function, the array and k. The array defines the data set and k dictates what percentile value is chosen from zero to one. Both of these arguments are required for this function to work.
The array can be entered as an array or a named range.
Inserting the PERCENTILE Function
The PERCENTILE function can be added to a cell with the use of an inserting method that Excel provides. If you have problems remembering how to use the syntax for this function, inserting it is the best option.
To use this method, a cell must first be selected. Next, the formulas tab needs to be selected, followed by clicking on the "More Functions" button on the Excel ribbon. Compatibility should be chosen along with the PERCENTILE selection from the list.
Next, a functions argument window appears where the array of data and kth percent needs to be added.
The array can be typed in or it can be selected from the spreadsheet by clicking the up arrow to the right of the array field. The kth percentile can be typed in decimal form or a cell reference can be chosen by clicking on the arrow to the right of that field.
PERCENTILE Function Syntax Examples
Below are some examples of the PERCENTILE function being used in varying situations.
=PERCENTILE(range1,.4) - 40th percentile
- Here the function uses a named range as an input for an array and the k chosen is .4. As a result, the 40th percentile of the named range range1 will display.
- Here the function uses a named range as an input for an array and the k chosen is D1. As a result, the percentile of range1 will display depending on the k that appears in cell D1.
- 5the function uses a named range as an input for an array and the k chosen is 80%. As a result, the percentile of the named range range1 will display the 80th percentile.
- In this final syntax example, the reference range C5:C14 serves as the array and the k is located in the cell reference E5.The percentile of the referenced range C5:C14 will display depending on the k that appears in cell E5.
As you can see from the above examples, there are a few different ways that the arguments can be displayed in the function.
Practical PERCENTILE Function Example
In this section I will walk you though a complete example of inserting the PERCENTILE function where the function is searched from a list of functions. The 20th percentile of a dataset will be found. See the simple data set in the illustration below.
To use the insert function a cell needs to be clicked and the formulas tab needs to be clicked.
Next, insert function can be clicked. Type percentile in the search bar that appears and select percentile from the list.
Next, the range that you want to find the percentile of needs to be selected after clicking in the array field. This is followed by adding a number between .1 and 1 after clicking the field labeled k.
Adding Functional Arguments
After the OK button is selected, the 20th percentile of the data set appears as a result.
This means that 2 is where 20% of the data is found. So, 2 is in the 20th percentile of the data set.
There are two other functions that are the used to find percentiles. More recently Microsoft has created new percentile functions that are specific in regards to including and excluding the percentile in the results.
Each of these functions is briefly described below.
PERCENTILE.EXC - This show the percentile excluding the percentile chosen in the results.
PERCENTILE.INC - This function will produce the same result as the the Percentile function because they both include the chosen percentile in the results.
Microsoft. (n.d.). PERCENTILE function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/percentile-function-91b43a53-543c-4708-93de-d626debdddca.
To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2020 Joshua Crowder