Select the last value from a list
 

Column A contains a list of numbers. This list will vary in length. Cell C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

=INDIRECT("A"&COUNT(A:A))

Regards,
Stefi


€žSteve€� ezt Ã*rta:

Column A contains a list of numbers. This list will vary in length. Cell C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

Try this:

=LOOKUP(99^99,A:A)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
Column A contains a list of numbers. This list will vary in length. Cell
C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

This is nicer and more secure than my solution, and it cleared an error in XL
Help, at least in the Hungarian version (so it can be a translation error).
Hugarian Help states that if LOOKUP doesn't find the value it returns the
greatest (legnagyobb) value in the lookup array. Your formula cleared that it
returns the LAST (utolsó) of the array!

Thanks,
Stefi


€žRagdyer€� ezt Ã*rta:

Try this:

=LOOKUP(99^99,A:A)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
Column A contains a list of numbers. This list will vary in length. Cell
C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

I think LARGEST is the good term instead of greatest!
Stefi


€žStefi€� ezt Ã*rta:

This is nicer and more secure than my solution, and it cleared an error in XL
Help, at least in the Hungarian version (so it can be a translation error).
Hugarian Help states that if LOOKUP doesn't find the value it returns the
greatest (legnagyobb) value in the lookup array. Your formula cleared that it
returns the LAST (utolsó) of the array!

Thanks,
Stefi


€žRagdyer€� ezt Ã*rta:

Try this:

=LOOKUP(99^99,A:A)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
Column A contains a list of numbers. This list will vary in length. Cell
C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

Try this

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

"Steve" wrote:

Column A contains a list of numbers. This list will vary in length. Cell C1
needs a formula to display the last (but not necessary largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

In my American (English) version, the Help file states:
(emphasis mine)
<<<"If LOOKUP can't find the lookup_value, it uses the largest value in the
array that is *LESS THAN OR EQUAL TO LOOKUP_VALUE*."

Now, my suggested formula uses an *extremely large* lookup value, which will
probably *never* exist in any lookup range (array), BUT ... and here's a
very significant BUT ... you should also remember that the lookup range is
supposed to be *SORTED, ASCENDING*.

SO ... If Lookup *can't* find that large lookup value, and it assumes that
the range is sorted, it *ALSO* assumes that the *LAST* number, not being
larger then the lookup value, *MUST* be the *largest value* less then the
lookup value.

That "quirk" in the Lookup function allows this type of formula to return
the *last* number in the lookup range, *NO MATTER* what it's actual value
might be.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Stefi" wrote in message
...
I think LARGEST is the good term instead of greatest!
Stefi


€žStefi€� ezt Ã*rta:

This is nicer and more secure than my solution, and it cleared an error
in XL
Help, at least in the Hungarian version (so it can be a translation
error).
Hugarian Help states that if LOOKUP doesn't find the value it returns the
greatest (legnagyobb) value in the lookup array. Your formula cleared
that it
returns the LAST (utolsó) of the array!

Thanks,
Stefi


€žRagdyer€� ezt Ã*rta:

Try this:

=LOOKUP(99^99,A:A)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Steve" wrote in message
...
Column A contains a list of numbers. This list will vary in length.
Cell
C1
needs a formula to display the last (but not necessary
largest/smallest)
figure in this list.

Any help would be appreciated

Select the last value from a list
 

Thanks for the explanation, nice trick!

Stefi


€žRagDyer€� ezt Ã*rta:

In my American (English) version, the Help file states:
(emphasis mine)
<<<"If LOOKUP can't find the lookup_value, it uses the largest value in the
array that is *LESS THAN OR EQUAL TO LOOKUP_VALUE*."

Now, my suggested formula uses an *extremely large* lookup value, which will
probably *never* exist in any lookup range (array), BUT ... and here's a
very significant BUT ... you should also remember that the lookup range is
supposed to be *SORTED, ASCENDING*.

SO ... If Lookup *can't* find that large lookup value, and it assumes that
the range is sorted, it *ALSO* assumes that the *LAST* number, not being
larger then the lookup value, *MUST* be the *largest value* less then the
lookup value.

That "quirk" in the Lookup function allows this type of formula to return
the *last* number in the lookup range, *NO MATTER* what it's actual value
might be.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Stefi" wrote in message
...
I think LARGEST is the good term instead of greatest!
Stefi


€žStefi€� ezt Ã*rta:

This is nicer and more secure than my solution, and it cleared an error
in XL
Help, at least in the Hungarian version (so it can be a translation
error).
Hugarian Help states that if LOOKUP doesn't find the value it returns the
greatest (legnagyobb) value in the lookup array. Your formula cleared
that it
returns the LAST (utolsó) of the array!

Thanks,
Stefi


€žRagdyer€� ezt Ã*rta:

Try this:

=LOOKUP(99^99,A:A)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Steve" wrote in message
...
Column A contains a list of numbers. This list will vary in length.
Cell
C1
needs a formula to display the last (but not necessary
largest/smallest)
figure in this list.

Any help would be appreciated