Need formula to retrieve last non-blank cell in range

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


"Eric" wrote:

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

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

"Ron Coderre" wrote:

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

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


"Eric" wrote:

> 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
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "Eric" wrote:
> >
> > > 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.

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

"Ron Coderre" wrote:

> 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
>
>
> "Eric" wrote:
>
> > 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
> >
> > "Ron Coderre" wrote:
> >
> > > 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
> > >
> > >
> > > "Eric" wrote:
> > >
> > > > 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.

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


"Eric" wrote:

> 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
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "Eric" wrote:
> >
> > > 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
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Eric" wrote:
> > > >
> > > > > 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.

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"? <

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


"Eric" wrote:

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

Helped me alot! Thank you!