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

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:A65535<0),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

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:A65535<0),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

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


--
Biff
Microsoft Excel MVP


"Richard Buttrey" <Richard wrote in
message ...
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:A65535<0),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

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

This is an array formula that must be entered using ctrl+shift+enter

=MIN(IF(ISNUMBER(G1:G10)*(G1:G10<0),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