Excel tools to demonstrate

PERCENTILE, QUARTILE Functions

We often like to rank the values in a data set in some way. For example, if you can the SAT exam to get into college, you essentially learn your ranking by a percentile. If you are at the 85^th percentile, you know that 85% of all people scored lower than you, and only 15% scored higher. Excel has two useful functions for finding this type of information: PERCENTILE and QUARTILE.

The QUARTILE function is probably the easiest to understand. Imagine that you have 1000 scores. You sort them from low to high and then divide them into 4 sets of 250 scores each, where the first set contains the smallest scores, the second set contains the next smallest scores, and so on. The first, second, and third quartiles are the breakpoints between these sets. For example, 25% of the scores are below the first quartile, and 75% are above it. (The second quartile is the median.)

To use the QUARTILE function:

Enter the formula =QUARTILE(datarange,k), where k is 1, 2, or 3. For example, if you enter 3 for k, you get the third quartile.

Try it! Find all three quartiles for the following data set (which extends well below row 10). Enter the formulas for these quartiles, labeled Q1, Q2, and Q3, in column D. (Scroll to the right to see the answers.)

The PERCENTILE function takes any percentage (expressed as a decimal number) and returns the value such that the given percentage of all values is below this value. For example, if we use the percentage 10% (0.10) and the PERCENTILE function returns 45, then we know that 10% of all values in the data set are below 45 and 90% are above it.

To use the PERCENTILE function:

Enter the formula =PERCENTILE(datarange,pct), where pct is any percentage expressed as a decimal, such as 0.10 for 10%.