Last number in a column.

  • Thread starter Thread starter Richard
  • Start date Start date
R

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
 
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?
Click to expand...


=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
 
This is an array formula that must be entered using ctrl+shift+enter

=MIN(IF(ISNUMBER(G1:G10)*(G1:G10<>0),G1:G10))
 
Thanks Ron - (and Don close on your heels),

Much appreciated

Kind regards

Richard
 
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
 
Back
Top