Field or formula to return last value in a column?

[Eric]

In Excel 2003, I have a list in a column. I'd like a function/formula that
returns the last value in the column.

For example, if the list looks like:
10
20
5
50
22

I'd like it to return 22.

Any suggestions?

Thanks.

 

[Mike H]

Eric

Try this and change the column to suit

=LOOKUP(6.022*10^23,A:A)

Mike

 

[Eric]

Actually, I reallize that I didn't correctly describe what I'm looking for.

I need to return the last NON-BLANK value in the target list.

So for example, if a1:7 reads:
5
1
15
22
7
(blank)
(blank)

I'd like the formula to return 7.

Thanks for your help. Sorry for the confusion.

 

[Mike H]

Did you try my formula?

Actually, I reallize that I didn't correctly describe what I'm looking for.

I need to return the last NON-BLANK value in the target list.

So for example, if a1:7 reads:
5
1
15
22
7
(blank)
(blank)

I'd like the formula to return 7.

Thanks for your help. Sorry for the confusion.

 

[Eric]

Yes, I did. I discovered that it worked for my need as I described it. Thanks
very much for your help.

A related question is, can you think of any formula that will stop at the
first blank cell? So, for example, if A1:7 is:

5
3
4
(blank)
1
(blank)
7

The formula would return 4.

I hope it doesn't seem that I'm wasting your time with random inquiries.
This is also something that I've actually needed to do.

Thank again.

 

[Mike H]

hi,

Alt+F11 to open Vb editor, right click 'ThisWorkbook' and insert module and
paste the code below in

call with

=Lastval("A1")


Function lastval(rng As String)
lastval = Range(rng).End(xlDown)
End Function


Mike

 

[Eric]

Thanks. I'm never really comfortable with VB, but I guess the way to do it is
just to do it. I ought to be more adventuresome. Thanks for the suggestion.

 

[Domenic]

A related question is, can you think of any formula that will stop at the
first blank cell? So, for example, if A1:7 is:

5
3
4
(blank)
1
(blank)
7

The formula would return 4.

First define the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=LOOKUP(BigNum,A1:INDEX(A1:INDEX(A:A,MATCH(BigNum,A:A)),MATCH(TRUE,A1:IND
EX(A:A,MATCH(BigNum,A:A))="",0)))