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. Search JabSto ::

Custom Search