Last number in a column.

[Richard]

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

 

[Ron Rosenfeld]

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

 

[Don Guillett]

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

=MIN(IF(ISNUMBER(G1:G10)*(G1:G10<>0),G1:G10))

 

[Richard Buttrey]

Thanks Ron - (and Don close on your heels),

Much appreciated

Kind regards

Richard

 

[T. Valko]

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

 

[Gord Dibben]

Biff

I'm with you on this and hope for an answer from OP.


Gord