Another of Excel’s new conditional formatting rules is the top/bottom rule, which applies a format to cells that rank in the top or bottom (for numerical items, the highest or lowest) values in a range. You can select the top or bottom either as an absolute value (for example, the top 10 items) or as a percentage (for example, the bottom 25 percent).

You can also apply formatting to those cells that are above or below the average. To create a top/bottom rule, begin by choosing Home, Conditional Formatting, Top/Bottom Rules. Excel displays six choices:

• Top 10 Items Choose this command to apply formatting to those cells with values that rank in the top X items in the range, where X is the number of items you want to see (the default is 10).
• Top 10% Choose this command to apply formatting to those cells with values that rank in the top X percentage of items in the range, where X is the percentage you want to see (the default is 10).
• Bottom 10 Items Choose this command to apply formatting to those cells with values that rank in the bottom X items in the range, where X is the number of items you want to see (the default is 10).
• Bottom 10 % Choose this command to apply formatting to those cells with values that rank in the bottom X percentage of items in the range, where X is the percentage you want to see (the default is 10).
• Above Average Choose this command to apply formatting to those cells with values that are above the average of all the values in the range.
• Below Average Choose this command to apply formatting to those cells with values that are below the average of all the values in the range.

In each case, you see a dialog box that you use to set up the specifics of the rule. For the Top 10 Items, Top 10%, Bottom 10 Items, and Bottom 10% rules, you use the dialog box to specify the condition and the formatting that you want applied to cells that match the condition. For the Above Average and Below Average rules, you use the dialog box to specify the formatting only.

This all works well and is straightforward to apply. However, none of these top/bottom rules might be quite right for your data analysis needs. For example, suppose your data set includes a few anomalous values that are skewing the average much higher or lower than it should be. In that case, a better measure of the “average” might be the median value, which is the value in a data set that falls in the middle when all the values are sorted in numeric order.

That is, half of the values fall below the median, and half fall above it. You calculate the median using the MEDIAN() function:

MEDIAN(number1[,number2,...])
number1, number2,… A range, array, or list of values of which you want the median.

For example, to calculate the median of the values in the sales list shown earlier in Figure 7.22, use the following formula:

=MEDIAN(D3:D21)

How do you get Excel to highlight cells that are, for example, below the median value? To handle this, Excel 2007 comes with another conditional formatting component that makes this feature even more powerful: You can apply conditional formatting based on the results of a formula. In particular, you set up a logical formula as the conditional formatting criteria.

If that formula returns TRUE, Excel applies the formatting to the cells; if the formula returns FALSE, instead, Excel doesn’t apply the formatting. In most cases, you use an IF() function, often combined with another logical function such as AND() or OR(). However, you can also use a simple comparison formula. For example, the following formula compares the value in cell D3 with the median of the value in the range D3:D21:

=D3 < MEDIAN($D$3:$D$21)

To apply such a formula to a range of cells for the purposes of conditional formatting, you need to bear in mind two more things:

• You must enter the reference to the comparison value that changes (such as D3 in the previous formula) as a relative reference.
• You must enter the reference to the comparison value that doesn’t change (such as the range $D$3:$D$21 above) as an absolute reference.

This way, when Excel applies the conditional formatting formula to each cell in the range, it adjusts the relative reference accordingly so that the formatting is correctly applied to each cell.
Here are the general steps to follow to set up formula-based conditional formatting:

1. Select the cells to which you want the conditional formatting applied.
2. Choose Home, Conditional Formatting, New Rule. Excel displays the New Formatting Rule dialog box.
3. Click Use a Formula to Determine Which Cells to Format.
4. In the Format Values Where This Formula is True range box, type your logical formula.
5. Click Format to open the Format Cells dialog box.
6. Use the Number, Font, Border, and Fill tabs to specify the formatting you want to apply and then click OK.
7. Click OK.

Figure 7.23 shows the conditional formatting formula applied to the range D3:D21 that formats those cells with values less than the median.

• How to find rotated die errors Last night my third and final eBay auction for my Washington $1 coin errors ended. The last coin was a...
• A Simple Pivot Point Trading System Discover a Forex Robot that made 2,300% NET PROFIT in 2009 and download the Forex Auto Detector Software FREE that...
• Top 10 Search Engine Listing And Ranking Tips There are many companies offering ‘first page ranking’ services guaranteeing top page listing in the major Search Engines for as...
• Scientists Turn Human Skin Cells Into Retinal Cells Image by richardmasoner via Flickr Scientists genetically “reprogrammed” human skin cells to possess the same properties as those that make...
• Guide to Fine Fragrance The more that you know about fine fragrance and everything that encompasses this topic, the better able you are going...