Excel PERCENTILE Formula (Table of Contents)
PERCENTILE Formula in Excel
Percentile is the value for which the percentage of data is forged.
PERCENTILE formula in excel is used for calculating the specific percentile value of the selected array. It returns the Kth value. Percentile formula in excel can be used to find what percentage of values are falling under Kth percentile value.
PERCENTILE function can only be used in Microsoft Excel version 2007 and earlier versions of it. As it is compatible for those versions only. For the latest version or the version which launched later stage of Excel 2007, we can use PERCENTILE.INC or PERCENTILE.EXC which gives a more accurate result with the same functional argument as compared to PERCENTILE formula or function in Excel. Although we can use all formats of PERCENTILE in all latest version of Excel as well.
How to Use PERCENTILE Formula in Excel?
Using PERCENTILE Formula in Excel is easy and convenient when we want to calculate n number of percentile for given data. Let’s take some examples to understand the usage of PERCENTILE Formula in Excel.
PERCENTILE formula can be used from the Insert Function which is located beside the formula bar by clicking on Insert Function icon.
Array = Range of data for which we need to calculate percentile,
k = Percentage value at which we need percentile.
PERCENTILE Formula in Excel -Example #1
We have sample data as shown below, which has a quality percentage of some workers. Where the quality ranges from 1% to nearly 90%. Now with the help of percentile, we will calculate how much percentage of data is marked in which percentile category.
As we can see in the above screenshot, we have a range of quality for which we need to file the percentile value at any point. Now for that, go to the cell where we need to see calculated percentile and select Insert Function located near formula bar as shown below.
Once we do that, we will get Insert Function box. From there, select the PERCENTILE function from All under the Or select a category box or search the category Statistic, from there select PERCENTILE function and then click on OK, as shown below.
Note – As recommended by Microsoft, we can use PERCENTILE.INC in place of PERCENTILE function. Which has same syntax and will give more accurate result. It is applicable for Excel 2013 and higher version.
After that, we will get a function argument box of Percentile function. As we can see, as per syntax here we need to select the Array and the K value. For example, let’s calculate the 40th Percentile. For that select the array. Here our array range is from cell A2 to A31 and for K value, enter 0.4 (40% Percentile Criteria). Once we do that, if our defined syntax is correct then we will see the result itself in the argument box as shown below. Once done, click on OK to get the result.
As we can see in the below screenshot, the calculated 40th Percentile is coming as 28.4, which means 28.4% of the selected values are under 40 Percentile range.
PERCENTILE Formula in Excel -Example #2
There is another method by which we can find Percentile value at any given percentage range. And in this example, we will see the PERCENTILE.INC function which is recommended by Microsoft in place of PERCENTILE with the same syntax but with more accurate result. For this, we will consider the same data set which we have seen in Example 1.
Now go to the cell where we need to see the output and type “=” sign (Equal) to go in edit mode of that cell. This will enable all the inbuilt functions in excel. From here we search for PERCENTILE.INC or PERCENTILE. As we can see in below screenshot, with same name Percentile, we got 5 functions. Now select PERCENTILE.INC which is the substitute of PERCENTILE function.
Even if we put the cursor on PERCENTILE.INC, it will show us the same significance which will be reflecting the same for PERCENTILE function. Once we select the PERCENTILE.INC, we will see the syntax of it which is the same as that of PERCENTILE function as shown below.
Now for Array, select the complete set of data. Here we are setting the data from cell A2 to A31. And for K value, let’s consider the 70th percentile, for that enter 0.7 in a value of K and press enter to see the result.
As we can see in below screenshot, the calculated 70th Percentile is coming as 55.3, which means 55.3% of the selected values are under 70 Percentile range.
For K value, if we enter a whole value in place of decimal then we will get an error message, as shown below.
This is the numerical error, which happens when we consider the incorrect numeric reference. And if we enter a text in place of decimal value then we will get an error message, as shown below.
This is an alphabetical text error, which happens when we select incorrect argument as text. So it is clear that for K value, we need to select the percentage in decimal numerical format, which we have seen in the above examples.
Also, keep the obtained percentile value in its original format. If we change its format into a percentage, then it will add an additional 100 digits along with percentage sign and the whole purpose of percentile will get changed, as demonstrated below.
Things to Remember
- If you are using Excel 2013 or higher version, then it is always recommended to use INC in place of PERCENTILE is the advanced and more accurate function with the same syntax to be written.
- K value should always be in decimal so that in the formula itself it will get converted into a percentage.
- The obtained percentile value will already be in Percentage. So it is not recommended to change the value into a percentage. If you do, you may end up adding more percentage to the obtained percentile value.
- Percentage and Percentile both are different. Percentile is the value for which the percentage of data is forged whereas Percentage is the amount of completion of anything.
This has been a guide to PERCENTILE Formula in Excel. Here we discussed How to use PERCENTILE Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles–