Microsoft Office Tutorials and References

In Depth Information

**Analyzing Data with Percentiles and Bins**

PERCENTILE.INC and PERCENTILE.EXC

The PERCENTILE functions are similar to the QUARTILE functions except you

can specify which percentile to use when returning a value. You aren’t locked

into fixed percentiles such as 25, 50, or 75.

PERCENTILE.INC (or PERCENTILE.EXC) takes two arguments:

✓
Range of the sample

✓
Value between 0 and 1

The value tells the function which percentile to use. For example, 0.1 is the

10th percentile, 0.2 is the 20th percentile, and so on.

Use the QUARTILE.INC function to analyze data at the fixed 25th, 50th, and

75th percentiles. Use the PERCENTILE.INC function to analyze data at any

desired percentile. PERCENTILE.EXC is used when the second argument is

exclusive of 0 and 1. In other words, the second argument can be any value

between 0 and 1 but not 0 or 1.

Figure 9-15 shows a sample of test scores. Who scored at or above the 90th

percentile? The highest-scoring students deserve some recognition. Bear in

mind that scoring at the 90th percentile is not the same as getting a score of

90. Values at or above the 90th percentile are those that are in the top 10

percent of whatever scores are in the sample.

Figure 9-15:

Using

PERCENTILE

to find high

scorers.