How to define and select the last (bottom) number in a column?

Thanks Gary, this works very well! But I'm not sure about the keystrokes
you've indicated... they did not appear to be necessary (and nothing
different happened when I tried the combination), and it worked fine with
just ENTER.

"Gary" wrote:

=LOOKUP(2,1/(A1:A65535""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

I'd use the formula Biff suggested! g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

No, the 1E10 is the lookup_value.

1E10 = 10,000,000,000 (10 billion)

The way that LOOKUP works is if the lookup_value is greater than any value
in the range then it returns the *last* value that is less than the
lookup_value. To insure that we get the *last* number in the range we use an
arbitrary huge number like 10 billion. If you we working with numbers that
big then we just use an even bigger number for the lookup_value like 1E100.
I don't even know what that value is called! g

Consider this example. Suppose you were working with bowling scores. The
highest possible bowling score is 300. In this case we know for certain what
kind of numbers we're dealing with so we don't need a huge arbitrary
lookup_value like 1E10. We just need a number that is guaranteed to greater
than 300. So, we can use 301.

=LOOKUP(301,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
wrote in message ...
This formula works very nicely! I also realize the "1E10" may be
substituted
with however many rows are to be searched; very interesting. Thank you
so
much for teaching me something new.

"T. Valko" wrote:

This will return the last numeric value in column A:

=LOOKUP(1E10,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

But that only finds the last number in a column even if there is text after
it (and throws an error if there isn't a number in the column)... Gary's
formula finds the last used cell in the column whether that cell has a
number or text in it (which is what my formula did also, but more
inefficiently, I would think)

Rick


"T. Valko" wrote in message
...
I'd use the formula Biff suggested! g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value
into a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the
column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

That's what the OP wants, the last NUMBER in the range.

which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535""),A1:A65535)

I'll do some tests later on tonight.


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

Rick


"T. Valko" wrote in message
...
I'd use the formula Biff suggested! g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that
Gary's formula can be committed by just using the Enter Key instead of
the key combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value
into a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the
column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

...use an even bigger number for the lookup_value like 1E100.
I don't even know what that value is called! g

It's called a "google"....

http://www.unc.edu/~rowlett/units/dictG.html

Rick

But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and for
some didn't let "a new number is added daily" register with me. Okay, then
yes, I would use your formula instead of Gary's... for this particular
questiong.


which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick

I stand corrected... I missed that you had said "a new number is added
daily"... use Biff's (T.Valko's) formula instead of Gary's for your problem.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

You are right. It works fine even if you just press enter.
Sorry for the confusion.

"BrendaN_at_Welke_Customs"
wrote in message ...
Thanks Gary, this works very well! But I'm not sure about the keystrokes
you've indicated... they did not appear to be necessary (and nothing
different happened when I tried the combination), and it worked fine with
just ENTER.

"Gary" wrote:

=LOOKUP(2,1/(A1:A65535""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Calculation times:

http://img229.imageshack.us/img229/8...actimesje1.jpg

Not much difference.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and for
some didn't let "a new number is added daily" register with me. Okay, then
yes, I would use your formula instead of Gary's... for this particular
questiong.


which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick