Field or formula to return last value in a column?
 

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.

Field or formula to return last value in a column?
 

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

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" wrote:

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

Did you try my formula?

"Eric" wrote:

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" wrote:

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

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" wrote:

Did you try my formula?

"Eric" wrote:

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" wrote:

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

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" wrote:

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" wrote:

Did you try my formula?

"Eric" wrote:

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" wrote:

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

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.

"Mike H" wrote:

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" wrote:

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" wrote:

Did you try my formula?

"Eric" wrote:

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" wrote:

Eric

Try this and change the column to suit

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

Mike

"Eric" wrote:

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.

Field or formula to return last value in a column?
 

In article ,
Eric wrote:

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)))

--
Domenic
http://www.xl-central.com