Is there a formula to select the last non-blank cell in a list

B

BROCK8292

Im trying to select the last date in each of about 1000 seperate 50 row lists
in a worksheet using a single cell at the top. Is there a formula that can do
this?
thank you,
barry
 
B

BROCK8292

thats incredible, thank you very much
im sure youre busy but if you get a chance
i would love to know why that works
br
 
F

Fred Smith

The formula tells Excel to find the number 1e100 in the range A2:A100. 1e100
is a very large number (10 to the 100th power). As there is no number that
large in the range, Excel returns the largest number it finds.

Regards,
Fred.
 
D

Dave Peterson

It returns the last number in that range--not the largest number in the range.
 
R

Ron Coderre

There's an error in your explanation:

With
A1: 2
A2: 500
A3: 10
A4: text

This formula: =LOOKUP(1E100,A2:A100)
returns 10....not 500

The way that LOOKUP works is:
.. It assumes the numbers in the list are in ascending order.
.. It searches through the list for the lookup value (ignoring text).
.. If the lookup value is larger than any item in the list,
it returns the last item in the list.

Note: This is a version that returns the
last text value (ignoring numbers):
=LOOKUP(REPT("z",255),A:A)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
B

BROCK8292

is there a way to return the largest number instead of the last?
that would actually work better for me
 
B

BROCK8292

thank you Ron,
is there a way to look up the largest number in that list,
that would actually be more effective for what i am trying to do
thanks for your time and effort,
barry

also, im trying to a range in one column based on a if the date in another
is any of the last 7 days, i want to repeat this about 1000 times, is there
an easier way to do this then with a nested sumif or a dsum formula?
 
Top