Page 387 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 344
Part II
Working with Formulas and Functions
The following array formula returns the contents of the last nonempty cell in the first 500 rows of column A:
{=INDEX(A1:A500,MAX(ROW(A1:A500)*(A1:A500<>””)))}
You can, of course, modify the formula to work with a column other than column A. To use a different col-
umn, change the four column references from A to whatever column you need. If the last nonempty cell
occurs in a row beyond row 500, you need to change the two instances of 500 to a larger number. The
fewer rows referenced in the formula, the faster the calculation speed.
You can[‘t use this formula, as written, in the same column with which it’s working.
CAUTION
CAUTION
Attempting to do so generates a circular reference. You can, however, modify it. For example,
to use the function in cell A1, change the references so that they begin with row 2 instead of row 1.
Returning the last value in a row
The following array formula is similar to the previous formula, but it returns the last nonempty cell in a row
(in this case, row 1):
{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))}
To use this formula for a different row, change the 1:1 reference to correspond to the row.
Ranking data with an array formula
Often, computing the rank orders for the values in a range of data is helpful. If you have a worksheet con-
taining the annual sales figures for 20 salespeople, for example, you may want to know how each person
ranks, from highest to lowest.
If you’ve used the Excel program’s RANK function, you may have noticed that the ranks produced by this
function don’t handle ties the way that you may like. For example, if two values are tied for third place, the
RANK function gives both of them a rank of 3. You may prefer a commonly-used approach that assigns each
an average (or midpoint) of the ranks — in other words, a rank of 3.5 for both values tied for third place.
Figure 18.8 shows a worksheet that uses two methods to rank a column of values (named Sales). The first
method (column C) uses the Excel RANK function. Column D uses array formulas to compute the ranks.
The following is the array formula in cell D5:
{=SUM(1*(B5<=Sales))-(SUM(1*(B5=Sales))-1)/2}
This formula is copied to the cells below it.
NOTE
NOTE Each ranking is computed with a separate array formula, not with an array formula entered
into multiple cells.
Each array function works by computing the number of higher values and subtracting one half of the num-
ber of equal values minus 1.

344