Ms Office Tune Up

When analyzing a data set, it’s often handy to rank where a certain value falls within the data. Excel has functions that enable you to calculate both the ordinal and percentage rank of each value in a set.

For the ordinal (first, second, third, and so on) rank, you use the RANK() function:

RANK(number, ref[, order])

number The number for which you want to find the rank.

ref A reference, range name, or array that corresponds to the set of values in which number will be ranked. (Note that ref must include number.)

order An integer that specifies how number is ranked within the set. If order is 0 (this is the default), Excel treats the set as though it was ranked in descending order; if order is any nonzero value, Excel treats the set as though it was ranked in ascending order.

For example, if a test result is in cell C3 and the full results are in the range C5:C35, then the following formula returns the ordinal rank of the test result:

=RANK(C3, C5:C35)

The ordinal rank is only occasionally useful. In data analysis, you’re more likely to work with the percentage rank. This is useful when you want to know what value in the set cor­responds to what percentile. For example, if you want to know what test scores fall in the top 10 percent, you’d calculate the 90th percentile, and every score above that is in the top 10 percent. To calculate the percentage rank, you use the PERCENTILE() function:

PERCENTILE(array, k)

array A reference, range name, or array of values for the set of data.

k The percentile, expressed as a decimal value between 0 and 1.

For example, if the test scores are in the range C5:C35, then the following formula calcu­lates the value at which the 90th percentile occurs:

=PERCENTILE(C5:C35, 0.9)

If you want to see which values are above or below a particular percentile, then you need to turn once again to Excel’s conditional formatting tools. For example, if you want to see only those values that are above the 90th percentile, then you can apply a custom highlight cells rule. Choose Home, Conditional Formatting, Highlight Cells Rules, More Rules to open the New Formatting Rule dialog box. In the Format Only Cells With section, choose Cell Value and Greater Than and then enter your PERCENTILE() function in the range box, as shown in Figure 1.

Figure 1 You can use the PERCENTILE() function to apply a conditional format.

What if you want to also see those values that lie within the 10th percentile? When you want to visualize multiple relationships in a data set, the ideal tool is Excel 2007’s new icon sets feature. Like data bars, you use icon sets to visualize the relative values of cells in a range. In this case, however, Excel adds a particular icon to each cell in the range, and that icon tells you something about the cell’s value relative to the rest of the range. For example,the highest values might get an upward pointing arrow, the lowest values a downward pointing arrow, and the values in between a horizontal arrow.

To apply an icon set to the selected range, choose Home, Conditional Formatting, Icon Sets, and then choose the set you want. For more complex operations, you can create a cus­tom icon set rule:

1. Select the cells to which you want the conditional formatting applied.
2. Choose Home, Conditional Formatting, Icon Sets, More Rules. Excel displays the New Formatting Rule dialog box.
3. Use the Icon Style list to click the icon set you want to use.
4. For all but the last icon in the set, choose an operator, a Value, and a value Type (Number, Percent, Formula, or Percentile). For example, Figure 3 shows the New Formatting Rule dialog box set up to show an upward pointing arrow in cells with val­ues above the 90th percentile, and a downward pointing arrow in cells with values below the 10th percentile.
5. Click OK.

Figure 3 shows the icon set rule from Figure 2 applied to a data set of gross domestic product growth rates.

Figure 2 An icon set rule that uses percentiles.

Figure 3 The conditional format­ting applied by the icon set rule shown in Figure 2.

The icon set formatting shown in Figure 3 is useful, but it does suffer from a drawback in this case: We’re not all that interested in the values that lie between the 10th and 90th percentiles (the ones formatted with horizontal arrows). Unfortunately, Excel doesn’t offer options for excluding particular icons from a set. However, you can work around this limi­tation. The trick is to create a second highlight cells rule that applies to the values you want to exclude and that does not apply any formatting. Here’s how to set it up:

1. Select the cells to which you want the conditional formatting applied.
2. Choose Home, Conditional Formatting, Highlight Cells Rules, More Rules. Excel displays the New Formatting Rule dialog box.
3. Choose Cell Value in the first list.
4. In the list of operators, choose Between. Excel displays two range boxes.
5. In the first range box, type the lower value that you want excluded from the format­ting. For example, to start the exclusion at the 10th percentile, you’d enter the follow­ing formula (where range is the range you’re formatting): =PERCENTILE(range, 0.1)
6. In the second range box, type the upper value that you want excluded from the for­matting. For example, to end the exclusion at the 90th percentile, you’d enter the fol­lowing formula (where, again, range is the range you’re formatting; see Figure 4): =PERCENTILE(range, 0.9)
7. Click OK. (Notice that we didn’t specify any formatting.)
8. Choose Home, Conditional Formatting, Manage Rules. Excel displays the Conditional Formatting Rules Manager.



Figure 4 This example uses PER­CENTILE()functions to specify the range of values to exclude from the conditional formatting.

9. The rule you just created should display above your Icon Set rule. If not, click the new rule and then click the up arrow to move it above the Icon Set rule.
10. Besides your new rule, click to activate the Stop If True dialog box, as shown in Figure 5.

NOTE

If you have multiple conditional format rules applied to a range, Excel applies the rules in order (from top to bottom as shown in the Conditional Formatting Rules Manager) one cell at a time. When you activate the Stop If True check box for a rule, Excel first applies the rule to the cell. If the rule returns FALSE, then Excel continues on to the next rule; if the rule return TRUE, instead, then Excel doesn’t apply any more rules to the cell.

11. Click OK. Excel reapplies the rules.

Figure 6 shows the result: The data displays with just the upward and downward point­ing arrows.

Figure 5 Move your no formatting rule to the top and activate the Stop If True check box.

Figure 6 The conditional format­ting rules from Figure 5 applied to the GDP data.

Technorati Tags: precentile based, icon sets

Popularity: 4% [?]