Trying to find a way to return the last value in column.

[lexadon]

i created a check register in excel but am having a hard time finding a way to return the last value in the column because of the formula currently in the column. any ideas of how to get it to return the last value in that column in a different column with the formula repeating it self down the column?

[AlKey]

The last value in a column can be found by using these formulas

If you values are text use:

=LOOKUP("zzzzz",A:A)

If numbers

=LOOKUP(10^308,A:A)

assuming data is in column A

[lexadon]

Thank you sooooooo much!!! That worked.

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Tony Valko]

The LOOKUP function will return the last (bottom-most) number if the lookup_value is larger than any number in the referenced range.

Typically, we suggest using gigantic lookup_values because we don't have any idea of the nature of the numbers in question.

For example, I typically suggest using 1E100 as the lookup_value. It's easy to remember, easy to type and it works.

=LOOKUP(1E100,A:A)

1E100 is a really gigantic number that is guaranteed to be greater than any number in the referenced range.

1E100 is scientific notation for the number 1 followed by 100 zeros. I don't even know what that number is called (if it even has a name).

Now, since you're needing this in your check register I can safely assume that your numbers are no where near 1E100. So, you simply need a number that is bigger than any number in the referenced range.

If your register balance will never be greater than say, 100,000, then you can use 100000 as the lookup_value.

If your register balance will never be greater than say, 10,000, then you can use 10000 as the lookup_value.

No need to use ridiculously gigantic numbers.

[AlKey]

1E100 is a really gigantic number that is guaranteed to be greater than any number in the referenced range.

1E100 is scientific notation for the number 1 followed by 100 zeros. I don't even know what that number is called (if it even has a name).

and actually it does have a name! It's googol

[Tony Valko]

and actually it does have a name! It's googol

If you do a Google search on 1E100 or 1E100 Excel you'll get lots of links to posts I've made!

[AlKey]

If you do a Google search on 1E100 or 1E100 Excel you'll get lots of links to posts I've made!

I hope you're not confusing Google with Googol. Googol is the name of the number. However, this is not the largest one. It is "1 followed by 100 zeros". There are Googolplex and then Googolplexian.

http://www.googolplexian.com/

[Tony Valko]

I hope you're not confusing Google with Googol.

Yes, I was.

It seems that Google is a play on the word Googol.