Microsoft Office Tutorials and References

In Depth Information

**Analyzing Data with Percentiles and Bins**

Here’s how to use the CORREL function:

1. Enter two lists of numbers.

The lists must be the same size.

2. Position the cursor in the cell where you want correlation to appear.

3. Enter
=CORREL(
to start the function.

4. Drag the pointer over the first list, or enter the address of the range.

5. Enter a comma (
,).

6. Drag the pointer over the second list, or enter the address of the range.

7. Enter a
)
and press the Enter key.

Analyzing Data with Percentiles

and Bins

No, not with trash bins (although you may want to throw your data out at

times)! The term
bins
refers to analyzing data by determining how many data

points fall into specified ranges, or bins.
Percentiles
is a technique for

analyzing data by determining where values relate, percentage-wise, to the entire

data set.

Imagine this: A pharmaceutical company is testing a new drug to lower

cholesterol. The data is 500 cholesterol readings from the people in the sample.

In Figure 9-14, the 500 cholesterol readings are in Column A. Of interest is

how the data breaks up with regard to the 25 percent, the 50 percent, and the

75 percent marks. That is, what cholesterol reading is greater than 25 percent

of the data (and therefore smaller than 75 percent of the data)? What value is

at the 50 percent position? These measures are called
quartiles
because they

divide the sample into four quarters.

QUARTILE.INC and QUARTILE.EXC

The QUARTILE function is designed specifically for this kind of analysis. The

function takes two arguments: One is the range of the sample data, and the

other indicates which quartile to return. The second argument can be a 0, 1,