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 with 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 gives a more accurate result with the same functional argument compared to the PERCENTILE formula or function in Excel. However, we can use all formats of PERCENTILE in all the latest version of Excel as well.
How to Use PERCENTILE Formula in Excel?
Using the 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 the 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 the calculated percentile and select Insert Function located near formula bar as shown below.
Once we do that, we will get the 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.
After that, we will get a function argument box of the 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 the 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 Microsoft recommends 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 the “=” sign (Equal) to edit that cell’s edit mode. This will enable all the inbuilt functions in excel. From here, we search for PERCENTILE.INC or PERCENTILE. As we can see in the below screenshot, with the same name Percentile, we got 5 functions. Now select PERCENTILE.INC, which is the substitute of the 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 the PERCENTILE function once we select the PERCENTILE.INC, we will see the syntax of it, which is the same as that of the 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 the 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 the below screenshot, the calculated 70th Percentile is coming as 55.3, which means 55.3% of the selected values are under the 70 Percentile range.
If we enter a whole value in place of decimal for the K value, 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 the decimal value, then we will get an error message, as shown below.
This is an alphabetical text error, which happens when we select an incorrect argument as text. It is clear that we need to select the percentage in decimal numerical format for the K value, 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 the 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 completing 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 a downloadable excel template. You can also go through our other suggested articles–