Need formula to retrieve last non-blank cell in range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric;
however, they're not sorted in numeric order. I could probably figure out
how to write a macro, but I'd prefer a formula. Any suggestions?

Thanks.
 
Try one of these:

Last numeric cell in Col_A:
=LOOKUP(10^99,A:A)

Last non-blank cell in Col_A:
=LOOKUP(2,1/(A1:A100<>""),A1:A100)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Ron - Thank you. Yes, your suggestion does solve my problem. But honestly,
it's a bit of a mystery why. The help text for LOOKUP says that the values
have to be in ascending order... but mine are not. So I don't know how it
works.

As to your second formula, it's even more mysterious. Why the 2 as the
first parameter? Also, I don't understand the syntax. What does the
"1/(A1:A100<>"")" mean? The help text indicates that the second parameter
must be a "lookup vector" which contains only one row or column. I don't
understand Excel's formula syntax. What does Excel mean by 1 slash an
expression containing a range? What am I missing?

I'm not a novice at this stuff, but I don't understand why this works.

Thanks for your help, and any additional explanation you can offer.

Eric
 
Regarding your questions about the LOOKUP function:

Per Exce help:
"If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value."

Using that information:
=LOOKUP(2,1/(A1:A100<>""),A1:A100)
In that formula, the second argument divides the number 1 by 1 or 0, the
numeric result of whether each cell is non-blank (1) or blank (0). 1/1
returns 1. 1/0 returns #DIV/0!. Consequently, when LOOKUP cannnot locate 2 in
the second array, it will return the last numeric item in the list that is
less than 2, which is the last 1. That item will refer to the last non-blank
cell in the third argument.

=LOOKUP(10^99,A:A)
The same rules apply in that formula. 10^99 is a much larger number than I
would ever expect in a worksheet. When the LOOKUP function cannot find it in
a list of numbers, it will return the last number in the list that is less
than 10^99.
Note: If you happen to be an astronomer or scientist, you may want to lookup
the largest number Excel can handle: 9.99999999999999E-307 instead of 10^99


Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Ron - Thank you, that does solve my problem.

I don't think there's any way to determine this from Excel help. The
sentence you quote indicates that the function will match the LARGEST value
that's less than or equal to the lookup value. It does not say that it will
match the LAST value. It also does says that the list must be sorted in
ascended order, and does not define what happens when the list is not sorted.

But in any case, it's understandable that the function would work this way,
even though it's undefined. It's a clever solution.

Regards,
Ora
 
Hi, Eric

I'm glad you liked the solution. I can't take credit for it, though; It's
been around for a while.

Regarding the Excel Help quote:
Help goes on to mention that if the list is not sorted, the results may be
unexpected. But, once you realize how it behaves in this usage...the results
are definitely expected. <g>

***********
Regards,
Ron

XL2002, WinXP
 
When you say "it's been around for awhile"... what's the location of
"around"? What I mean is, is there any compilation of these
undocumented-but-true features?

I suspect there's no canonical source... but if you can point me in the
right direction(s) to read more about Excel tricks and techniques, maybe I
can get started in this direction. Or should I just continue to use Google
as my starting place for research into the lore of this secret fraternity?

Thanks.
Eric
 
Eric

Regarding:
what's the location of "around"? <
Click to expand...

My friend, you're already there.
It's impossible for any book or website to cover every potential situation
an Excel user might encounter. You could scan hundreds of websites and read
thousands of pages on Excel tricks, tips, and techniques and still not find
what you need. The most efficient way to get the information you need when
you need it is to tap into the amazing pool of talent in these newsgroups.

***********
Best Regards,
Ron

XL2002, WinXP
 
Back
Top