Lookup last in column formulas

[L. Howard Kittle]

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

 

[Biff]

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

 

[Aladin Akyurek]

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

 

[Aladin Akyurek]

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]

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

 

[L. Howard Kittle]

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

 

[Biff]

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