Creating a formula that references the last value of a column

[Guest]

Hey,
I was wondering if there was any way to create a formula that references the
last cell with a value in a column. This excel worksheet we have is updated
with a new value for the 2007 column every week, therefore we are just copy
and pasting our simple formulas one cell down each week. I was wondering if
there was any way to set a formula up that would just take the last cell that
has an entry in it and then start our formulas with that reference point. If
you need further clarification, just write back. Thanks.

 

[Gord Dibben]

conny

=MATCH(10^10,A:A) to get the last number in a column

=LOOKUP(REPT("z",255),B:B) to get the last non-number in a column

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) to get either number or text


Gord Dibben MS Excel MVP

 

[Guest]

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

will return the last numeric value in column A. For a really good
discussion of the topic, see:

http://www.xldynamic.com/source/xld.LastValue.html#last_numeric

 

[Guest]

Thanks Gord,
That worked. Now I'm not sure if you can do this, but I need to take this
last value that I find and subtract it by a value that is in the same row two
columns over. Is there a way to make sure that the value I am taking is in
the same row as that last value of the other column?

 

[Guest]

I figured that question out... but is there any way to reference the cell
directly above the last cell, so I can take the most recent's week and
subtract it by the previous week's?

 

[Stan Brown]

Sun, 24 Jun 2007 12:01:02 -0700 from conny

I was wondering if there was any way to create a formula

Please don't post the same query multiple times.

 

[Ragdyer]

You can use this to get the last number in, say Column A:

=LOOKUP(99^99,A:A)

And use this to get the row number of that last number:

=MATCH(99^99,A:A)

SO, to get the *next to last* row number:

=MATCH(99^99,A:A)-1

To get that value in that *next to last* row number:

=INDEX(A:A,MATCH(99^99,A:A)-1)

Finally, to subtract the last number by the next to last number:

=LOOKUP(99^99,A:A)-INDEX(A:A,MATCH(99^99,A:A)-1)

 

[Guest]

Thanks... I guess I'm an idiot! I appreciate the help!

 

[Guest]

All right one last question... I need to figure out how to reference the cell
below in another column. I know how to reference the same row in another
column but when I try to reference the cell below that by doing >
=MATCH(99^99,A:A)+1, it returns N/A, and then if I put dashes in, it works,
but for some reason it will return the very last value of the other row.
What should I do? Basically currently I have cell T29 and I want to
reference cell R30, but next week it will be T30, and R31.

 

[Guest]

never mind, I was using a look up and then i changed to an index function and
it worked... Thanks for all the help!

 

[Ragdyer]

You're welcome, and glad to see you were able to work things out.