=LOOKUP(100^10,D:D)

  • Thread starter Thread starter Gilbert
  • Start date Start date
Returns the last numeric value in column D.

=LOOKUP(100^10,D:D)

Assumes the last number in column D will not exceed the value of 100 raised
to the 10th power.

Ignores blanks in column D and will not return a text value even if it is
the last entry in column D.

HTH
Regards,
Howard
 
=LOOKUP(100^10,D:D)
What does 100^10 mean?
Click to expand...

100^10 means 100 to the 10th power:

100*100*100*100*100*100*100*100*100*100

Or:

100,000,000,000,000,000,000

As you can see this is a very large number!

The way that LOOKUP works (in this application) is if every number in col D
is less than the lookup value 100^10 then the formula returns the *last*
number in col D that
is less than the lookup value 100^10. Unless you're the richest person on
the face of the
earth then chances are pretty good that none of the numbers in col D will be
anywhere near the value 100^10 so the result of the formula is the *last*
number in the range that is less than the lookup value.

In essence, 100^10 is a very large *arbitrary* number that we can safely
assume will be greater than any value in col D allowing the formula to
return the correct result.

Lately, I've been using this expression: 1E100

=LOOKUP(1E100,D:D)

1E100 is an even larger number than 100^10. It's 1 followed by 100 zeros.
The advantage to using 1E100 is that it's both a huge number and it's a
constant value. 100^10 has to calculate but 1E100 doesn't, it's a constant
value.

You may see formulas using this technique and the number
9.99999999999999E+307:

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

This formula works exactly the same way, it's just using the largest number
that
can be entered in a cell as the *arbitrary* huge number.

I prefer to use the easier to enter 1E100 as opposed to
9.99999999999999E+307. I don't want to have to count how many 9's I have to
type in!
 
Back
Top