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.
