selecting the penultimate cell from a column of data

[jfp]

=IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH("*",A:A,-1),
IF(ISERROR(MATCH("*",A:A,-1)),MATCH(9.999999E+306,A:A),
MAX(MATCH(9.999999E+306,A:A),MATCH("*",A:A,-1))))

I have the above function which allows me to select the final data I have
inputted. What do I need to add in order to get the last but one piece of
data? Is this possible?

I create tables which have todays price, last weeks price and the price from
the corresponding week last year too (so having the option for 52 cells above
the last one would also be useful). These prices are all listed in a table
which I then need to summarise - hence the request.

Many thanks

James

 

[Mike H]

Hi,

Your formula is returning the row number so putting -1 on the end will
return the row number of the second to last row. You could use this instead
which does the same

=SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))-1

Mike

 

[Jacob Skaria]

The below array formula will return the last but one row number in ColA with
an entry. Please note that this is an array formula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=LARGE(IF(A1:A1000<>"",ROW(A1:A1000)),2)

If this post helps click Yes

 

[jfp]

Thank you for those ... sorry, I copied the original suggestion which gives
the row number as my formula is actually below(with an INDEX in order to give
the value of the cell). I'll see if the suggestions still works .

=INDEX('[QMS Data
Spreadsheet.xls]Cattle'!R:R,IF(ISERROR(MATCH(9.999999E+306,'[QMS Data
Spreadsheet.xls]Cattle'!R:R)),MATCH("*",'[QMS Data
Spreadsheet.xls]Cattle'!R:R,-1),IF(ISERROR(MATCH("*",'[QMS Data
Spreadsheet.xls]Cattle'!R:R,-1)),MATCH(9.999999E+306,'[QMS Data
Spreadsheet.xls]Cattle'!R:R),MAX(MATCH(9.999999E+306,'[QMS Data
Spreadsheet.xls]Cattle'!R:R),MATCH("*",'[QMS Data
Spreadsheet.xls]Cattle'!R:R,-1)))))

Thanks