how do you calculate percentile in excel

The Excel PERCENTILE function

The Excel PERCENTILE function calculates the “kth percentile” for a set of data. A percentile is a value below which a given percentage of values in a data set fall. A percentile calculated with .4 as k means 40% percent of values are less than or equal to the calculated result, a percentile calculated with k = .9 means 90% percent of values are less than or equal to the calculated result.

To use PERCENTILE, provide a range of values and a number between 0 and 1 for the “k” argument, which represents percent. For example:

=PERCENTILE(range,.4) // 40th percentile
=PERCENTILE(range,.9) // 90th percentile

You can also specify k as a percent using the % character:

=PERCENTILE(range,80%) // 80th percentile

PERCENTILE returns a value greater than or equal to the specified percentile.

In the example shown, the formula in G5 is:

=PERCENTILE(scores,E5)

where “scores” is the named range C5:C14.

Note: Microsoft classifies PERCENTILE as a “compatibility function”, now replaced by the PERCENTILE.INC function.

Percentile IF in table

Excel RANK Function

The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time).

PERCENTILE function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 More…

Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

Important:

• This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.
• For more information about the new functions, see PERCENTILE.EXC function and PERCENTILE.INC function.

Syntax

PERCENTILE(array,k)

The PERCENTILE function syntax has the following arguments:

• array     Required. The array or range of data that defines relative standing.
• k  Required. The percentile value in the range 0..1, inclusive.

Remarks

• If k is non-numeric, PERCENTILE returns the #VALUE! error value.
• If k is < 0 or if k > 1, PERCENTILE returns the #NUM! error value.
• If k is not a multiple of 1/(n – 1), PERCENTILE interpolates to determine the value at the k-th percentile.

What is the PERCENTILE Function?

The PERCENTILE function is categorized under Excel Statistical functions. PERCENTILE will return the k-th percentile of the values in a particular range.

In corporate finance, we can use the function to analyze, for example, the number of employees who scored above a certain percentile on a test.

In MS Excel 2010, the PERCENTILE function was replaced by the PERCENTILE.INC function. However, PERCENTILE is still available in current versions of Excel (stored in the list of compatibility functions).

Formula

=PERCENTILE(array,k)

The PERCENTILE function uses the following arguments:

1. Array (required argument) – This is the array or range of data that defines the relative standing.
2. k (required argument) – The percentile value in the range 0…1, inclusive.

How to use the PERCENTILE Function in Excel?

As a worksheet function, PERCENTILE can be entered as part of a formula in a cell of a worksheet.

Percentage

In mathematics, a percentage (from Latin per centum “by a hundred”) is a number or ratio expressed as a fraction of 100. It is often denoted using the percent sign, “%”,^[1][2] although the abbreviations “pct.”, “pct” and sometimes “pc” are also used.^[3] A percentage is a dimensionless number (pure number); it has no unit of measurement.

Examples

For example, 45% (read as “forty-five percent”) is equal to the fraction 45/100, the ratio 45:55 (or 45:100 when comparing to the total rather than the other portion), or 0.45. Percentages are often used to express a proportionate part of a total.

(Similarly, one can also express a number as a fraction of 1000, using the term “per mille” or the symbol “‰”.)

Example 1

If 50% of the total number of students in the class are male, that means that 50 out of every 100 students are male. If there are 500 students, then 250 of them are male.

Example 2

An increase of $0.15 on a price of $2.50 is an increase by a fraction of 0.15/2.50 = 0.06. Expressed as a percentage, this is a 6% increase.

While many percentage values are between 0 and 100, there is no mathematical restriction and percentages may take on other values. For example, it is common to refer to 111% or −35%, especially for percent changes and comparisons.

resource: wikipedia

A few notes about the PERCENTILE function:

1. #NUM! error – Occurs if either:
   1. The supplied value of k is less than 0 or greater than 1.
   2. The supplied array is empty.
2. #VALUE! error – Occurs if the supplied value of k is non-numeric.
3. If k is not a multiple of 1/(n – 1), PERCENTILE interpolates to determine the value at the k-th percentile.