Select the last value from a list

[Steve]

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

 

[Stefi]

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

Regards,
Stefi


„Steve†ezt írta:

 

[Ragdyer]

Try this:

=LOOKUP(99^99,A:A)

 

[Stefi]

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:

 

[Stefi]

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


„Stefi†ezt írta:

 

[Teethless mama]

Try this

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

 

[RagDyer]

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.

 

[Stefi]

Thanks for the explanation, nice trick!

Stefi


„RagDyer†ezt írta: