find last cell in column - without VBA

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

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:A15>0)*ROW(A1:A15))))

HTH,
Bernie
MS Excel MVP
 
Thanks again, I really appreciate this one, I never thought of combining
these functions to come up with this.

Harold
 
One way:

=LOOKUP(2,1/(A1:A15>0),A1:A15)

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
It worked great, thanks,

Harold


Sandy Mann said:
One way:

=LOOKUP(2,1/(A1:A15>0),A1:A15)

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to delete rows with zero value 4
w/s Formula to Automatically Create Two 2-equi-interval Tables ?? 14
Trying to delete info in a cell 5
Finding values based on current month 3
Problem with a conditional Min formula 1
Userform sorting data 4
Hide rows using IF 1
Table sorting questions 1

Back
Top