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