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?

 

[Zack Barresse]

yup. like this...

Book3.xls
	A	B	C	D
1	2	3
2	5
3	6
4	10
5	3
6	9
7	3
8	4
Sheet5

formula is:

=INDEX(A:A,MATCH(9.9999999E+307,A:A)-1)

key is the -1.

hth

 

[njws]

Here's a challege, what if the data is horizontal instead of vertical? I used =HLOOKUP(9.99999999E+307,2:2,1) for the last item, but the -1 doesn't work in this situation.

 

[GorD]

firefytr, would your solution work if the cell above was blank.

Is there any chance of that?

This was my effort - probably over kill

Book1
	B	C	D	E
1	Numbers
2	5
3	7		lastvalue	6
4			2ndlast	9
5	4
6	8
7	9
8
9
10	6
Sheet1

 

[Brian from Maui]

Here's a challege, what if the data is horizontal instead of vertical? I used =HLOOKUP(9.99999999E+307,2:2,1) for the last item, but the -1 doesn't work in this situation.

Try,

=LOOKUP(9.9999999999999E+307,2:2)-1

 

[njws]

That one (=LOOKUP(9.9999999999999E+307,2:2)-1) takes the last value and subtracts one.

 

[Brian from Maui]

That one (=LOOKUP(9.9999999999999E+307,2:2)-1) takes the last value and subtracts one.

Sorry, misread the question,

=INDEX(2:2,MATCH(9.99999999999999E+307,2:2)-1)

 

[Zack Barresse]

GorD is right, as an alternative to his wonderful solution...

Book3.xls
	B	C	D	E
1	Numbers
2	6
3	7		lastvalue	4
4			2ndlast	7
5	4		w/oOFFSET	7
6	8
7	9
8	7
9
10	4
Sheet5

formula is:

=INDEX(B:B,MATCH(9.9999999999E+307,INDIRECT("b1:b"&MATCH(9.99999999E+307,B:B)-1)))

i just hate that OFFSET

 

[Aladin Akyurek]

Using firefytr's formula is there a way to return the second to last value? I'm specifically interested in the numeric version.

Ultimate goal: Return the last value, the second to last value, and the change between the two.

Activate Insert|Name|Define.
Enter BigNum as name in the Names in Workbook box.
Enter the following uncrippled big number in the Refers to box:

9.99999999999999E+307

Click OK.

The following gives you the last numeric value in a range, referred to as Range...

=LOOKUP(BigNum,Range)

Range can be horizontal or vertical and definite like A2:A10 or B2:F2 or indefinite like A:A or 3:3.

The following will give you second last numeric value:

=LOOKUP(BigNum,INDEX(Range,MATCH(BigNum,Range)-1))

 

[GorD]

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

 

[fairwinds]

Aladins formula can handle blanks.