Choose last number in a column

D

Don

I have a formula where one of the components is the last number in a column.
I need to know how to always call the last value in a column that will be
added to every day.
 
D

Don

Thank you. Just so that I know, what does the 100^10 mean? If you don't
mind responding one more time.
 
T

T. Valko

It means 100 to the 10th power or 1 followed by 20 0s!
100,000,000,000,000,000,000

The way that LOOKUP works is if the lookup_value is greater than any value
in the lookup_vector it will return the *last* value that is less than the
lookup_value. There's a pretty good chance that no number in your range is
greater than 100^10 so the result is the *last* number in the range.

Basically, we use an arbitrary gigantic number like 100^10 when we don't
know what the possible values might be. All you really need is a number that
is greater than the highest number in your range. For example, suppose you
were working with bowling scores. The highest possible bowling score is 300
so the highest possible number in your range can only be 300. So you'd only
need a lookup_value of 301 for the formula to work:

=LOOKUP(301,A:A)

You could also do something like this:

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

But you really don't need to use another function call and calculation when
it's easier to just use something like 301 or 100^10.

Biff
 
Top