Get final non-blank cell in range
 

I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

One way:

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

Biff

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest
or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest
or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

OR ... something like:

=LOOKUP(99^99,A:A)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"L. Howard Kittle" wrote in message
. ..
Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest
or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

1. The help text for LOOKUP says the values have to be in order. But in my
case, they won't always be. Will it still work even if not in order?

2. What if the values are a mix of numbers and alpha values?

"Biff" wrote:

One way:

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

Biff

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest
or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

Based on this statement:

Not the highest or lowest, simply the last.

I assumed you were talking about NUMBERS in which case the formula would
work whether the numbers were sorted or not.

2. What if the values are a mix of numbers and alpha values?

If you want the last value, text or number (excludes formula blanks: ""):

=LOOKUP(2,1/(A1:A20<""),A1:A20)

Biff

"Eric" wrote in message
...
1. The help text for LOOKUP says the values have to be in order. But in
my
case, they won't always be. Will it still work even if not in order?

2. What if the values are a mix of numbers and alpha values?

"Biff" wrote:

One way:

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

Biff

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a
formula
which will return the last value non-blank in the range. Not the
highest
or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

Or even:

=LOOKUP(MAX(A:A)+1,A:A)

Biff

"RagDyer" wrote in message
...
OR ... something like:

=LOOKUP(99^99,A:A)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"L. Howard Kittle" wrote in message
. ..
Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a
formula
which will return the last value non-blank in the range. Not the
highest or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

Harlan might say:
"Unneeded function call."
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Biff" wrote in message
...
Or even:

=LOOKUP(MAX(A:A)+1,A:A)

Biff

"RagDyer" wrote in message
...
OR ... something like:

=LOOKUP(99^99,A:A)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"L. Howard Kittle" wrote in message
. ..
Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a
formula
which will return the last value non-blank in the range. Not the
highest or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

Harlan might say:
"Unneeded function call."

Or Aladin!

Yeah, but if the range is "small" it's a lot less confusing than
9.99999999999999E+307 or 99^99.

Biff

"RagDyeR" wrote in message
...
Harlan might say:
"Unneeded function call."
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Biff" wrote in message
...
Or even:

=LOOKUP(MAX(A:A)+1,A:A)

Biff

"RagDyer" wrote in message
...
OR ... something like:

=LOOKUP(99^99,A:A)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"L. Howard Kittle" wrote in message
. ..
Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then
you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a
formula
which will return the last value non-blank in the range. Not the
highest or
lowest, simply the last.

Thanks.

Get final non-blank cell in range
 

I'm prejudiced!
I think:

=LOOKUP(99^99,A:A)

Looks neat!<bg

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Biff" wrote in message
...
Harlan might say:
"Unneeded function call."

Or Aladin!

Yeah, but if the range is "small" it's a lot less confusing than
9.99999999999999E+307 or 99^99.

Biff

"RagDyeR" wrote in message
...
Harlan might say:
"Unneeded function call."
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Biff" wrote in message
...
Or even:

=LOOKUP(MAX(A:A)+1,A:A)

Biff

"RagDyer" wrote in message
...
OR ... something like:

=LOOKUP(99^99,A:A)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"L. Howard Kittle" wrote in message
. ..
Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then
you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard

"Eric" wrote in message
...
I've got a range where I'm including a bunch of values. I need a
formula
which will return the last value non-blank in the range. Not the
highest or
lowest, simply the last.

Thanks.