Return last value?

[Bob Newman]

This is probably very simple but I have a column of numbers whose length
keeps growing do to additions to it. I need a formula to be placed
elsewhere in the worksheet that will return the last value in that column
(it is not necessarily the largest number in the column).

 

[Paul B]

Bob, give this a try, will return the last number in column A

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Ken]

Paul, if you have am oment, could you please tell me what
the 9.999... argument does? (How does it work?) I can't
figure it out.
TIA.

 

[Aladin Akyurek]

MATCH() with its match-type argument omitted or set to 1 does run a binary
search.

=INDEX(A:A,MATCH(MAX(A:A)+1,A:A))

would effect the same result. Using Excel's largest value instead of
MAX(Range)+1 makes a much faster formula (no additional function call).
Moreover, MAX() has to examine every cell, a fact that requires additional
time.

BTW, a shorter version of:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

is

=LOOKUP(9.99999999999999E+307,A:A))

 

[Ken]

Sorry, but I am a little slow in getting this.
My understanding:
In the MATCH function the second argument shown here is
A:A and represents an array defined by column A,, which
has 65,536 rows.
The first argument in the MATCH function is supposed to
be the value for which you are searching and MATCH is
supposed to return the location of that value.
The first argument shown here is the 9.999.....E+307.
However, the MATCH part of the answer returns the row
number of the last numerical entry in column A.

1. Am I understanding that the 9.999.... represents the
largest value represented in Excel?

2. If the first arguemnt is supposed to be the value
searched for in the array, I still don't understand how
the largest Excel value makes this happen in this case?

 

[Bob Newman]

Thanks... that did it.

Bob

Bob, give this a try, will return the last number in column A

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[BadgerWI]

How can I modify this formula to refer to another worksheet in the same
file?

Bob, give this a try, will return the last number in column A

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Frank Kabel]

Hi
use
=LOOKUP(9.99999999999999E+307,'other_sheet'!A:A))

--
Regards
Frank Kabel
Frankfurt, Germany

How can I modify this formula to refer to another worksheet in the same
file?

Bob, give this a try, will return the last number in column A

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

that