Excel 2010 Bible

Part II: Working with Formulas and Functions

392

This formula uses the COUNTA function to count the number of nonempty cells in column A. This

value (minus 1) is used as the second argument for the OFFSET function. For example, if the last

value is in row 100, COUNTA returns 100. The OFFSET function returns the value in the cell 99

rows down from cell A1 in the same column.

If column A has one or more empty cells interspersed, which is frequently the case, the preceding

formula won’t work because the COUNTA function doesn’t count the empty cells.

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 dif-

ferent column, 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.

Caution
You can’t use this formula, as written, in the same column with which it’s working. Attempting to do so gener-
ates 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. n

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 work-

sheet containing 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 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, RANK 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.