Page 246 - Excel Data Analysis

12 537547 Ch11.qxd 3/4/03 12:48 PM Page 232

EXCEL DATA ANALYSIS

DETERMINE RANK AND PERCENTILE

f you want to rank a series of values in a list, you can use input range, but only the first row or column of values
the Rank and Percentile Tool. With this tool, Excel takes within the range is analyzed. You can only have a label in
I a specified list of numeric values and ranks them from the first row of a column. If the specified range contains any
highest to lowest by both a numeric and a percentage other text, an error message displays.
value. It also calculates a percentile for your value, which is You can output the results of the Rank and Percentile tool
how an individual score ranks compared to other scores. to a specific range of cells within the current worksheet, a
For example, you may want to rank the sales from different new worksheet, or a new workbook. If you select New
sales people within the organization to not only show Worksheet, you can specify the worksheet name or allow
which person had the highest sales but also determine the Excel to assign a default name.
sales person's percentile, or rank, when compared to the
entire sales team. This feature is perfect for ranking the top Excel provides the Rank and Percentile tool as part of the
selling item, the most efficient facility within a company, or Analysis ToolPak add-in option that you can load on the
the machine or team that produces the most product. Add-Ins dialog box. See the section "Install Excel Add-Ins"
for more information on loading add-in options.
You can only rank one row or column of values at a time.
Excel allows you to select multiple rows or columns as the

DETERMINE RANK AND PERCENTILE

$B$1:$B$12

Rank and Percentile

⁄ Create the range of data ■ The Data Analysis dialog ■ The Rank and Percentile Á Specify whether data
to analyze. box displays. dialog box displays. values are grouped in rows
or columns.
¤ Click Tools ➪ Data ‹ Click Rank and Percentile. ˇ Specify the range of cells
Analysis. to analyze.
› Click OK.
232