Locate the last number in a spreadsheet and copy it into a formula

LOCATE THE LAST NUMBER IN A SPREADSHEET AND COPY IT INTO A FORMULA
A client needs to keep track of frequently changing commodity prices and capture the most current figure and eventually copy it into a formula that calculates its resell price. I’m sure Excel can perform that process, but I can’t figure it out. Can you help?

So what you want is a formula that locates the last number in a column or row. An Excel array formula can perform the search and then drop the target number in any formula you wish. Here’s how it’s done:

If the column with prices starts at cell E1, the basic formula to find the last number is:
=INDEX(E1:E65000,MAX(IF(LEN(E1:E65000)> 0,ROW(E1:E65000) ,0)),1)

The 65,000 in the formula represents the maximum number of cells in a column in Excel 2003, which assumes you want to reserve that much space (from E1 to E65000 ) for the price list; if you want to reserve fewer cells in the column, just use a smaller number. If you’re using Excel 2007, the maximum is a whopping 1,048,576; in that case, substitute the E65000 with E1048576 .

Important : After entering the INDEX formula in the cell you want the number to appear in, you must then press Ctrl+Shift+Enter—a step that encloses the entire formula in curly brackets—{ }—and converts it into a powerful array formula (see screenshot below).

If you want to store the prices horizontally, that is, along the cells in a row, not down a column, and the first price is in A2, the formula looks like this:
=INDEX(A2:IV2,1,MAX(IF(LEN(A2:IV2)> 0,COLUMN(A2:IV2),0)))

Note that the number 1, which was in the other formula, is not in this formula.

To learn how to use array formulas in many more ways see “ The Power of Arrays ,” JofA , March 07, page 52, by Paul Goldwater and Timothy Fogarty.