Greetings everyone,

I just looking around and found on some other post a suggestion to
find the last value of a series of data in a column (Col E in this
case), which is:

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

I'm trying to understand how this formula works; I read the help
file's content for the Lookup function and it is still not clear to me
how it works in this case. I'd appreciate if someone could explain
this to me. Thanks in advance!

The LOOKUP function will return the next highest value if it is unable to
find an exact match in the range. So in this case, it is unlikely that your
date will ever have a value higher than (9.999x10^307), so it will return the
value before, which will be the last value.

The reason why it doesn't return a result equivalent to the MAX() function
is that the LOOKUP functions assumes that the data range is already in
ascending order, so it'll assume that the last value in its data range is the
biggest, hence returning the last value.

I hope that helps, apoligies if I didn't phrase it more eloquently.

Regards,

Harimau

--
Location: Sydney
Occupation: Actuarial Consultant (Project Finance/Financial Modeling)


"JuanMarin" wrote:

Greetings everyone,

I just looking around and found on some other post a suggestion to
find the last value of a series of data in a column (Col E in this
case), which is:

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

I'm trying to understand how this formula works; I read the help
file's content for the Lookup function and it is still not clear to me
how it works in this case. I'd appreciate if someone could explain
this to me. Thanks in advance!

Harimau,

that's great! so the function is used based on that assumption. It all
makes sense to me now.

Thanks a lot!