=LOOKUP(100^10,G:G)
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 G
is less than 100^10 then the formula returns the *last* number in col G that
is less than 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 G will be
anywhere near the value 100^10 so the result of the formula is the *last*
number in the range.

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

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

=LOOKUP(1E100,G:G)

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,G:G)

This formula works exactly the same, 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
...
Hi Biff,

Thank you for this formula, but could you please explain it to me? What
does 100^10 mean?

"T. Valko" wrote:

Assuming column G is the balance column:

=LOOKUP(100^10,G:G)

Will return the last numeric value from column G.

Biff

"Janet" wrote in message
...
How do I show a check register balance which changes with each entry in
a
designated single cell so I can easily link it to another document?
--
Janet