Hadi Ul Bashar
If you’re working on statistical data, you may require to calculate Percentile every now and then. There are several ways of calculating Percentiles. In this article, I’m going to demonstrate to you how to calculate percentile from Mean and Standard Deviation in Excel. You can check the following image to get an overview of what we’re going to do in this article.
Download Practice Workbook
You can download our practice workbook from here for free!
What Is a Percentile?
We use the word Percentile frequently in our day-to-day life. A formal definition of Percentile looks like the following definition. Percentile is a number where a certain percentage of scores fall below that number. For example, if you’ve scored 80 out of 100 on a test, and your score is 95 Percentile, then you’ve scored better than 95% of the examinees who took the test.
We can use the NORM.INV, PERCENTILE.INC and PERCENTILE.EXC functions individually to calculate the Percentile. And you need to use them according to your use cases. We’ll discuss them in detail in this article.
How to Calculate Percentile from Mean and Standard Deviation in Excel: Step-by-Step
Suppose, we have a sales dataset. This dataset includes Salesperson ID, Salesperson, Product, Product ID, and Total Sales. So our dataset looks like the following image.
Now we need to calculate the percentile related to Total Sales from this dataset.
Step 1: Calculate Mean Value
In this case, we’re going to get the Percentile associated with Total Sales. First thing first let’s calculate the mean value of Total Sales.
- Type the following formula in cell C17 and hit ENTER to get the Mean from this dataset.
=AVERAGE(F5:F14)
Read More: How to Calculate Average and Standard Deviation in Excel
Step 2: Calculate Standard Deviation
Let’s calculate the standard deviation now. For that, type the following formula in cell C18 and hit ENTER to get the standard deviation from this dataset.
=STDEV.P(F5:F14)
Read More: How to Calculate Population Standard Deviation in Excel
Step 3: Calculate Percentile from Mean and Standard Deviation
If we have the Mean and Standard Deviation values, we can calculate the Percentile using these values. To do so, we can use the NORM.INV function. This function takes the probability, mean, and standard deviation as input and returns the inverse of the normal cumulative distribution as output.
- Now, let’s say we need to get the 90% Percentile Value. Insert 90% in cell C19.
Now we have all the data to calculate the Percentile from Mean and Standard Deviation.
- Type the following formula in cell C20 and hit ENTER to get the Percentile.
=NORM.INV(C18,C16,C17)
In this way, we’ve calculated the Percentile from the Mean and Standard Deviation. From the image, we can see that 235.3356604 is the value that is more than 90% of the sales value for our dataset.
Read More: How to Calculate Standard Deviation of a Frequency Distribution in Excel
How to Calculate Percentile Directly in Excel: 2 Useful Methods
We can directly get the Percentile too. Remember, this value may vary from the Percentile we’ve calculated just now as here we’re not going to use the Mean and Standard Deviation.
We can calculate the Percentile using the PERCENTILE.INC function or using the PERCENTILE.EXC function. I’m going to demonstrate both here.
1. Calculate Percentile Applying PERCENTILE.INC Function
We can use the PERCENTILE.INC function to get the Percentile. Note that it includes all the data points in the datasets including the first and last data points.
For example, we need to find out the 90% percentile value. To do so, follow the task I’m showing here.
Steps:
- Type the following formula in cell C18 and press ENTER to get the Percentile.
=PERCENTILE.INC(F5:F14,C16)
From the image, we can say that 210 is the value that is greater than 90% of our Total Sales value. Note that it includes all the data in our dataset including the first and last data points.
Read More: Generate Random Number with Mean and Standard Deviation in Excel
2. Calculate Percentile Using PERCENTILE.EXC Function
We can also use the PERCENTILE.EXC function to calculate the percentile. Note that, PERCENTILE.EXC returns the k-th (exclusive) percentile of an expression value from a dataset. Now we’ll use the PERCENTILE.EXC function to calculate the 0.9 percentile. The steps to do so are as follows.
Steps:
- Type the following formula in cell C18 and press ENTER to calculate the Percentile.
=PERCENTILE.EXC(F5:F14,C16)
As a result, we’ve got the 0.9 percentile value and it is 290. Note that, this is the percentile value excluding the first and last data points from our dataset.
Takeaways from This Article
If you’ve followed this article thoroughly, now you should be able to:
- Calculate percentile from Mean and Standard Deviation using the NORM.INV function.
- Use the PERCENTILE.INC and PERCENTILE.EXC functions according to your need.
Things to Remember
While working on finding the percentile value, you should keep some aspects in mind. For example, as I’ve already mentioned that there is a difference between the PERCENTILE.EXC and PERCENTILE.INC functions, you should be well aware of which function you should use. Also, these functions only take number-type data as input, so keep an eye on the data type you’re passing as the arguments of these functions.
Conclusion
In this article, I’ve demonstrated how to calculate percentile from mean and standard deviation in Excel. In this regard, I’ve demonstrated the use of NORM.INV function. I’ve also shown the use of PERCENTILE.INC and PERCENTILE.EXC functions to calculate the percentile directly. You may now make a decision about which function we should use in our dataset. But remember, if you’re using the mean and standard deviation to calculate the percentile, then NORM.INV is the only possible option. I hope you will be able to use these functions properly in your use cases. If you face any issues regarding this, please let me know in the comment section. I’ll be happy to help you. Have a good day!
