How to calculate percentiles of a data group in microsoft excel? Step by step guide

Excel is the most widely used spreadsheet and table program on home and office computers . This ambiguity means that it has functions that are easy to use and, at the same time, more complex ones . They all cohabit in the same program.

In this sense, it is normal for the less expert to be unaware of certain tools. However, the Internet offers us a window to knowledge. So in this post we will show you how to use one of the utilities most used by statesmen .

To know everything about percentiles and how it applies in various situations, we recommend that you read the content of the following text.

What are percentiles and what are these data for in statistical operations?

The percentile is a statistical measure that is used to determine the projection of a population based on characteristic traits that its behavior has . It is widely used in medicine for children, since it can be established according to their age and gender, the probable height when they have a specific unit and how their current height is with respect to the total of children of their age.

That is, the population as a whole is taken, the data is ordered from smallest to largest and its projection is analyzed. Then it is compared to the statistical stratum that needs to be studied and the percentage that is below or above the projection is determined . You should not forget that the percentile is studied in a frequency distribution with a base equal to 1 .

So the values ​​that it can take are from 1 to 99 and it is represented as the percentage that is below the observations. That is, if a 35th percentile (P35) is established, it is interpreted as 35% of the observations meet the characteristics of the general population. Therefore, the 50th percentile will be equal to the median in the frequency distribution, which may or may not coincide with the average . In turn, the P25 will be equal to the first quartile and the P75 to the third quartile.

What aspects should be taken into account when calculating the percentiles?

Some of the aspects that you must take into account when calculating the percentiles is that you will have to work with a frequency distribution equal to 1 and that they, to be interpreted, have to be related to the percentages. They are also linked to quartiles and deciles, since the normal distribution can be divided into 4 or 10 parts. This means that every time a percentile is studied what you are analyzing is a location on the Gaussian bell .

For this reason, to work with percentiles you must have the data ordered from lowest to highest because otherwise there would not be a normal frequency distribution. Finally, so that you can make comparisons between the cases you want to analyze, both must be homogeneous . By this we mean that you will be able to compare, for example, if you use it in children’s medicine, to people who are the same age and gender.

Steps to calculate a percentile in an Excel spreadsheet correctly

The steps that you will have to perform to calculate a percentile in an Excel spreadsheet are the following: 

  • Enter the data for the group you want to analyze.
  • If you wish, you can  graph by means of a histogram so that you have a better understanding of the distribution.
  • Next, head over to the Formulas tab and select the More Features option .
  • Find the Statistics functions and then click on PERCENTILE.EXC .
  • A window will open in which you must choose the matrix and the percentile number . You must bear in mind that Excel works in both by one, so if you are looking for 5% you will have to write 0.05 .
  • Finally, you will have to interpret the result based on the percentage chosen for the analysis.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button