Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

Returning the last value in a column

Suppose that you have a worksheet that you update frequently by adding new data to columns.

You may need a way to reference the last value in column A (the value most recently entered). If

column A contains no empty cells, the solution is relatively simple and doesn’t require an array

formula:

=OFFSET(A1,COUNTA(A:A)–1,0)

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 column A:

{=INDEX(A:A,MAX(ROW(A:A)*(A:A<>””)))}

You can, of course, modify the formula to work with a column other than column A. To use a

different column, change the column references from A to whatever column you need.

You can’t use this formula, as written, in the same column in which it’s working.

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 rather than the entire columns. For example, use A2:A1000 to return the last

non-empty cell in the range A2:A1000.

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 containing the annual sales figures for 20 salespeople, for example, you may want to know

how each person ranks, from highest to lowest.