Geting the last x values from a column

Adrian · Jan 16, 2004

I'm interested, for instance, allways to get the last 30 values of a
series of values from one column. Everyday, there are more and more
records, no constant number. Is it possible ?

Thanks

Peo Sjoblom · Jan 16, 2004

Assuming it won't be any empty rows in-between

select let's say A1:A30 on the sheet where you want the last 30 and with A1
active array enter
(enter with ctrl + shift & enter) this formula

=OFFSET(Sheet2!$A$1,COUNTA(Sheet2!A:A)-1,,-30)

adapt to fit your ranges

Frank Kabel · Jan 16, 2004

Hi Adrian

it is possible :-)

For this you use a combination of OFFSET and COUNTA

e.g. you want to sum the last 30 entries in column A use the following
=SUM(OFFSET($A$1,COUNTA($A:$A)-30,0,30,1))

also have a look at
http://www.cpearson.com/excel/excelF.htm#DynamicRanges

HTH
Frank

Adrian · Jan 19, 2004

Peo Sjoblom said:
Assuming it won't be any empty rows in-between

select let's say A1:A30 on the sheet where you want the last 30 and with A1
active array enter
(enter with ctrl + shift & enter) this formula

=OFFSET(Sheet2!$A$1,COUNTA(Sheet2!A:A)-1,,-30)

adapt to fit your ranges

--

Regards,

Peo Sjoblom

Thanks a lot both of you guys.....it works now...both solutions are good...
Regards,

Adrian

Adrian · Jan 19, 2004

Frank Kabel said:
Hi Adrian

it is possible
For this you use a combination of OFFSET and COUNTA

e.g. you want to sum the last 30 entries in column A use the following
=SUM(OFFSET($A$1,COUNTA($A:$A)-30,0,30,1))

also have a look at
http://www.cpearson.com/excel/excelF.htm#DynamicRanges

HTH
Frank

Thanks a lot guys...both responses are good.....
regards,

Adrian