How to find the last number in a variable length column.

[TonyS52]

I am recording a running total of my bank balance. Col 1 money in, col 2 money out, col 3 running total. As I enter each transaction the column gets longer but I would like to see the running total at the top of the spread sheet instead of scrolling down.

[LeoTaxi]

so you place the runnig total @ top


Cheers
Leo

[Tony Valko]

This will return the last (bottom-most) numeric value from column C:

=LOOKUP(1E100,C:C)

[protonLeah]

Put the Totals row at the top and use dynamic named ranges in the formulas to auto-adjust for column height.

[TonyS52]

Many thanks Biff-nice and simple. Could you explain how it works?

[Tony Valko]

The way LOOKUP works is...

If *every* value in the range is less than the lookup_value (1E100) then the
formula returns the *last* value in the range that is less than the
lookup_value.

To make sure that we do in fact get the last value we use a lookup_value
that is guaranteed to be greater than any value in the range. So, we use an
arbitrary HUGE number for the lookup_value.

1E100 is a HUGE number. It's 1 followed by 100 zeros. Chances are pretty
good that you don't have any numbers that big in your range so the formula
returns the desired result, the last number in the range.

Technically, all you really need for a lookup_value is a number greater than
any number in your range. Consider this, say you were working with bowling
scores. The highest possible bowling score is 300 so no number in your range
will be greater than 300. So, in that case a lookup_value of 301 is all that
is needed.

When people post these types of questions they ususally don't tell us how
big the numbers are that they're dealing with so when we reply, to be on the
safe side, we use a HUGE lookup_value.