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
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
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,