Microsoft Office Tutorials and References

In Depth Information

**Using the Rank And Percentile tool**

constants). If you’d rather create formulas linked to the input values, you can use the

builtin FREQUENCY array function, which calculates the number of times specified values occur

in a population and takes the arguments
data_array
and
bins_array
. Figure 17-11 shows the

FREQUENCY function applied to the data shown in Figure 17-8.

To use the FREQUENCY function, set up a column of bin values, just as you would with the

Histogram tool, and then select the entire range where you want the output to appear,

which in our example is G2:G21—the cells in column G that are directly adjacent to the bin

values in column F. (This range must be a column because FREQUENCY can’t use a row or

multicolumn range as its output range.) Next, type the formula, specifying the input range

as the first argument and the bin range as the second. Press Ctrl+Shift+Enter to lock in the

array formula. For more information about arrays, see “Using arrays” in Chapter 12.

Figure 17-11
Use the FREQUENCY function to link the distribution analysis to the input data.

Using the Rank And Percentile tool

Suppose you want to rank the scores shown in Figure 17-8. You could rank them by sorting

the data in descending order, with the best score at the top and the worst score at the

bottom of the column. To find the rank of any score, you might want to create an ascending

series of numbers beside the sorted scores, with
1
beside the best score and
1,000
beside

the worst.