If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Last number in a column.
Hi
I have a range of numbers in a column, say 4,7,4,0,9,8,2,0,0,0,0 I need a function to find the last number in the column excluding the last series of zeros which are in cells that haven't com in to play yet. i.e. in this example I need the function to return 2. Please note there is a valid zero within the range which gives problems if I try and use a count of zeros and deduct this from a COUNTA() all cells, within an indirect function. Any ideas please? Regards *** I'm using an evaluation license of nemo since 38 days. You should really try it! http://www.malcom-mac.com/nemo |
#2
|
|||
|
|||
Last number in a column.
On 18 Feb 2008 15:42:08 GMT, Richard wrote:
I have a range of numbers in a column, say 4,7,4,0,9,8,2,0,0,0,0 I need a function to find the last number in the column excluding the last series of zeros which are in cells that haven't com in to play yet. i.e. in this example I need the function to return 2. Please note there is a valid zero within the range which gives problems if I try and use a count of zeros and deduct this from a COUNTA() all cells, within an indirect function. Any ideas please? =LOOKUP(2,1/(A1:A655350),A1:A65535) will return the last non-zero entry in column A, excluding A65536. Note that, at least in versions earlier than Excel 2007, about which I don't know, you cannot specify a column range that includes the entire column in this formula. --ron |
#3
|
|||
|
|||
Last number in a column.
This is an array formula that must be entered using ctrl+shift+enter
=MIN(IF(ISNUMBER(G1:G10)*(G1:G100),G1:G10)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Richard" wrote in message ... Hi I have a range of numbers in a column, say 4,7,4,0,9,8,2,0,0,0,0 I need a function to find the last number in the column excluding the last series of zeros which are in cells that haven't com in to play yet. i.e. in this example I need the function to return 2. Please note there is a valid zero within the range which gives problems if I try and use a count of zeros and deduct this from a COUNTA() all cells, within an indirect function. Any ideas please? Regards *** I'm using an evaluation license of nemo since 38 days. You should really try it! http://www.malcom-mac.com/nemo |
#4
|
|||
|
|||
Last number in a column.
Thanks Ron - (and Don close on your heels),
Much appreciated Kind regards Richard "Ron Rosenfeld" wrote: On 18 Feb 2008 15:42:08 GMT, Richard wrote: I have a range of numbers in a column, say 4,7,4,0,9,8,2,0,0,0,0 I need a function to find the last number in the column excluding the last series of zeros which are in cells that haven't com in to play yet. i.e. in this example I need the function to return 2. Please note there is a valid zero within the range which gives problems if I try and use a count of zeros and deduct this from a COUNTA() all cells, within an indirect function. Any ideas please? =LOOKUP(2,1/(A1:A655350),A1:A65535) will return the last non-zero entry in column A, excluding A65536. Note that, at least in versions earlier than Excel 2007, about which I don't know, you cannot specify a column range that includes the entire column in this formula. --ron |
#6
|
|||
|
|||
Last number in a column.
Biff
I'm with you on this and hope for an answer from OP. Gord On Mon, 18 Feb 2008 14:01:27 -0500, "T. Valko" wrote: note there is a valid zero within the range How would you distinguish that the first 0 is valid but the others are not? 4,7,4,5,9,8,2,0,0,0,0 |
Thread Tools | |
Display Modes | |
|
|