Can someone explain the above formula please?

Thank you in advance

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

"Gilbert" wrote in message
...
Can someone explain the above formula please?

Thank you in advance

=LOOKUP(100^10,D:D)
What does 100^10 mean?

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!

--
Biff
Microsoft Excel MVP


"Gilbert" wrote in message
...
Can someone explain the above formula please?

Thank you in advance