Microsoft Excel is an indispensable tool for any entrepreneur or small-scale business owner. It is essential for executing the necessary calculations for your business. The most useful Excel function is the PERCENTILE.EXC function. The PERCENTILE.EXC function computes the “kth percentile” for a data set where k is 0 to 1.
What is a percentile?
A percentile represents a number where a certain percentage of scores falls below that number. For example, if you are the fifth tallest person in a group of 20, 75% of people are shorter than you, so you are at the 75th percentile.
In other words, PERCENTILE.EXC function will skim through a vast data set and pinpoint the exact number, which helps segregate the data into your chosen percentiles. On the other hand, Microsoft Excel also includes PERCENTILE.INC function. Although it is less accurate, it comes in handy in many situations.
You can learn percentile calculation and much more in the free Exploring and Producing Data for Business Decision-Making course
, where all students will learn how to calculate percentile and Z scores.
Calculate percentile in Excel.
How to Calculate Percentiles in Excel?
To find the percentile in Excel, you must enter a formula into the cell “=PERCENTILE. EXC(A1:AX,k)” without quotes.
“X” represents the last row in column “A,” where you have entered data
“k” represents the percentile value from 0 to 1.
The k percentile represents a value below which the k percent of values in the data set fall.
A percentile calculated with k = 0.5 means 50% percent of values are less than or equal to the calculated result.
How to Find Percentile in Excel?
- Open a new worksheet in Microsoft Excel.
- Go ahead and click on cell “A1”. Then, enter the values into the cells in column A.
- Click the “B1” cell.
- Enter =PERCENTILE.EXC(A1: AX,k) into the cell. “X” denotes the last row in column “A.” So you will have to enter the data in column “A.”And “k” is the percentile value you are looking for. The percentile value for “k” must range between 0-1.Example: If you want to find the value for the 60th percentile, you will use “0.6” as your percentile value.
- Click “Enter” to complete your formula. Lastly, check the value in “B1.”
How to calculate p50 p75 p90 in Excel?
To calculate p50, you need to use the “=PERCENTILE. EXC(A1:AX,k)” formula where “X” is the last row in column “A” where you have entered data and “k” is 0.5.
To calculate p75, you need to use the “=PERCENTILE. EXC(A1:AX,k)” formula where “X” is the last row in column “A” where you have entered data and “k” is 0.75.
To calculate p90, you need to use the “=PERCENTILE. EXC(A1:AX,k)” formula where “X” is the last row in column “A” where you have entered data, and “k” is 0.9.
Answers for our members:
Can I get Excel on my Chromebook and calculate the percentile in Excel?
You can download Microsoft Excel on a Chromebook as a Microsoft Office App. You can also download only the specific Microsoft Excel App on your Chromebook without installing the whole Microsoft Suite. After that, you can use Excel and formulas as usual. For example, you can calculate the percentile without a problem.
Conclusion
In business, percentile calculation can be necessary to interpret test results. For example, SAT scores or COMAT score percentiles are critical so that test-takers can compare their performance to that of other students.
Learn more about authors in Nimblefreelancer's team biography page.