find last cell in column - without VBA
 

Hi,

Apart from using VBA, is there any way to write a formula that will find the
last used cell in a column of 15 cells? In the first set below, it would be
..388, in the second set it would be .133. Any help would be appreciated.

0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.313
0.388



0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000

Harold,

One way, for numbers in A1:A15:

=INDEX(A1:A15,SUMPRODUCT(MAX((A1:A150)*ROW(A1:A15 ))))

HTH,
Bernie
MS Excel MVP


"Harold Good" wrote in message ...
Hi,

Apart from using VBA, is there any way to write a formula that will find the last used cell in a
column of 15 cells? In the first set below, it would be .388, in the second set it would be .133.
Any help would be appreciated.

0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.313
0.388



0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000

Thanks again, I really appreciate this one, I never thought of combining
these functions to come up with this.

Harold


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Harold,

One way, for numbers in A1:A15:

=INDEX(A1:A15,SUMPRODUCT(MAX((A1:A150)*ROW(A1:A15 ))))

HTH,
Bernie
MS Excel MVP


"Harold Good" wrote in message
...
Hi,

Apart from using VBA, is there any way to write a formula that will find
the last used cell in a column of 15 cells? In the first set below, it
would be .388, in the second set it would be .133. Any help would be
appreciated.

0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.313
0.388



0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000

One way:

=LOOKUP(2,1/(A1:A150),A1:A15)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Harold Good" wrote in message
...
Hi,

Apart from using VBA, is there any way to write a formula that will find
the last used cell in a column of 15 cells? In the first set below, it
would be .388, in the second set it would be .133. Any help would be
appreciated.

0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.313
0.388



0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000

It worked great, thanks,

Harold


"Sandy Mann" wrote in message
...
One way:

=LOOKUP(2,1/(A1:A150),A1:A15)

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Harold Good" wrote in message
...
Hi,

Apart from using VBA, is there any way to write a formula that will find
the last used cell in a column of 15 cells? In the first set below, it
would be .388, in the second set it would be .133. Any help would be
appreciated.

0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.313
0.388



0.006
0.000
0.036
0.125
0.133
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000