Lookup last in column formulas
 

Hello Excel users and experts,

Is there any significant difference in these formulas that return the last
value in the column.

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

Why use fourteen 9's past the decimal point, either with or without the E
when three 9's with or without the E works just as well?

Thanks
Howard

Hi!

Good question.

Here's a bone of contention I have when I see these types
of formulas.

Say for example that you know for certain that the
absolute largest number that could possibly in your range
is 100. It's not possible for a value greater than 100 to
be in your range of values. So, why use this formula:

=LOOKUP(9.99999999999999+307,A:A)

when this formula will do:

=LOOKUP(101,A:A)

or even this:

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

When the average user see's that number,
9.99999999999999+307, they freak out!

And yes, I know why *some* use that large a number!

Biff

-----Original Message-----
Hello Excel users and experts,

Is there any significant difference in these formulas
that return the last
value in the column.

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

Why use fourteen 9's past the decimal point, either with
or without the E
when three 9's with or without the E works just as well?

Thanks
Howard


.

Howard,

Under the topic "Excel specifications and limits" in the Excel Help, we
read:

Largest number allowed to be typed into a cell 9.99999999999999E+307

That's a known constant. We can define, say, BigNumber, as referring to
this constant and use it in the relevant formulas. Theoretically, it's
the less probable number to occur in the ranges of interest. Having it
as such would also minimize questions emamating from variations.

Aladin

L. Howard Kittle wrote:
Hello Excel users and experts,

Is there any significant difference in these formulas that return the last
value in the column.

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

Why use fourteen 9's past the decimal point, either with or without the E
when three 9's with or without the E works just as well?

Thanks
Howard

Invoking...

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

instead of

=LOOKUP(BigNumber,A:A)

introduces an unnecessary performance loss. Recall that MAX has to
examine every cell to produce a reference-dependent big number, while
9.99999999999999E+307 practically guarantees what is needed so that we
get we want (the last numerical value) in a few steps.

Biff wrote:
Hi!

Good question.

Here's a bone of contention I have when I see these types
of formulas.

Say for example that you know for certain that the
absolute largest number that could possibly in your range
is 100. It's not possible for a value greater than 100 to
be in your range of values. So, why use this formula:

=LOOKUP(9.99999999999999+307,A:A)

when this formula will do:

=LOOKUP(101,A:A)

or even this:

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

When the average user see's that number,
9.99999999999999+307, they freak out!

And yes, I know why *some* use that large a number!

Biff


-----Original Message-----
Hello Excel users and experts,

Is there any significant difference in these formulas

that return the last

value in the column.

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

Why use fourteen 9's past the decimal point, either with

or without the E

when three 9's with or without the E works just as well?

Thanks
Howard


.

While your point is of course valid, I seriously doubt
that under the majority of circumstances that one would
notice an unnecessary performance loss.

This is something I struggle with, overkill versus
practicality.

Biff

-----Original Message-----
Invoking...

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

instead of

=LOOKUP(BigNumber,A:A)

introduces an unnecessary performance loss. Recall that
MAX has to
examine every cell to produce a reference-dependent big
number, while
9.99999999999999E+307 practically guarantees what is
needed so that we
get we want (the last numerical value) in a few steps.

Biff wrote:
Hi!

Good question.

Here's a bone of contention I have when I see these
types
of formulas.

Say for example that you know for certain that the
absolute largest number that could possibly in your
range
is 100. It's not possible for a value greater than 100
to
be in your range of values. So, why use this formula:

=LOOKUP(9.99999999999999+307,A:A)

when this formula will do:

=LOOKUP(101,A:A)

or even this:

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

When the average user see's that number,
9.99999999999999+307, they freak out!

And yes, I know why *some* use that large a number!

Biff


-----Original Message-----
Hello Excel users and experts,

Is there any significant difference in these formulas

that return the last

value in the column.

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

Why use fourteen 9's past the decimal point, either
with

or without the E

when three 9's with or without the E works just as well?

Thanks
Howard


.

.

Hi Folks,

Thanks for the good discussion. Always good information in this forum. May
not always understand it fully, but you can count on getting the full scoop!

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Is there any significant difference in these formulas that return the last
value in the column.

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

Why use fourteen 9's past the decimal point, either with or without the E
when three 9's with or without the E works just as well?

Thanks
Howard

Unfortunately, your question didn't get answered. I don't
know the answer as I'm not very knowledgable on
exponential notation but I just thought I'd raise a
related point of my own.

Here's another related question:

Is it exponential notation or scientific notation?

Biff

-----Original Message-----
Hi Folks,

Thanks for the good discussion. Always good information
in this forum. May
not always understand it fully, but you can count on
getting the full scoop!

Regards,
Howard

"L. Howard Kittle" wrote in
message
...
Hello Excel users and experts,

Is there any significant difference in these formulas
that return the last
value in the column.

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

Why use fourteen 9's past the decimal point, either
with or without the E
when three 9's with or without the E works just as well?

Thanks
Howard



.