last item in a column

[Caius]

Hi there, it's me again, Excel n00b.

Just wondering if there's a formula for retrieving the last item in a column and then storing that value into another cell?

 

[Aladin Akyurek]

Aladin, how would you tackle this if data contains blanks - any different?

The formulas I posted are not affected by empty cells, cells housing error values or logical values or text. They will simply give the last numeric value and second last numeric value. Did I miss something regarding the original post?

 

[GorD]

I have had a play and still can't get this to work. i amusing the trial data

4
6

7
8
5


2
4

8

I can get the formula to return the last numeric cell, but can't get the second formula to work, if the cell above is blank. If I fill in a value immediately above the last entry it works. Any ideas what I am doing wrong

 

[Zack Barresse]

which formulas don't work?

 

[Aladin Akyurek]

I have had a play and still can't get this to work. i amusing the trial data

4
6

7
8
5


2
4

8

I can get the formula to return the last numeric cell, but can't get the second formula to work, if the cell above is blank. If I fill in a value immediately above the last entry it works. Any ideas what I am doing wrong

You're not doing anything wrong. I see why you had touble with the second formula. That formula is incomplete...

Book1
	A	B	C	D
1	4		8
2	6		4
3
4	7
5	8
6	5
7
8
9	2
10	4
11
12	8
13
Sheet1

C1:

=LOOKUP(BigNum,A1:A12)

C2:

=LOOKUP(BigNum,$A$1:INDEX(A:A,MATCH(BigNum,A:A)-1))

The bolded part is missing in my initial post.

 

[GorD]

Ah, thanks for that, I knew it would be a fairly simple fix and better than my effort using offset.