Averaging last 5 cells in a column
 

Hi folks,

In my workbook, I have a bunch of worsheets, where I'm average all numbers
in one column. I'd like to add one more stat, to where I'd like to average
the last 5 #'s in a column. Only thing is that no column ends on the same
row, so I can't specifiy specific cells..

Need some help to develop an average formula to pick the last five entries
in a column.

TIA.

Bob

Averaging last 5 cells in a column
 

=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-4,0,5))

if there are no blank cells in the column

"Bob Smith" wrote:

Hi folks,

In my workbook, I have a bunch of worsheets, where I'm average all numbers
in one column. I'd like to add one more stat, to where I'd like to average
the last 5 #'s in a column. Only thing is that no column ends on the same
row, so I can't specifiy specific cells..

Need some help to develop an average formula to pick the last five entries
in a column.

TIA.

Bob

Averaging last 5 cells in a column
 

Thanks so much for that timely reply Toppers :).

I'm doing it with column B, so I modified your formula to
=AVERAGE(OFFSET(INDIRECT("B" & COUNTA(B:B)),-4,0,5)) . It does an average,
but not the right average. I pull down this data from a web query and cells
B1 & B2 are empty, and cell B3 is the title for the column.

Any help on modifying the formula is appreciated.

Regards, Bob


"Toppers" wrote in message
...
=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-4,0,5))

if there are no blank cells in the column

"Bob Smith" wrote:

Hi folks,

In my workbook, I have a bunch of worsheets, where I'm average all
numbers
in one column. I'd like to add one more stat, to where I'd like to
average
the last 5 #'s in a column. Only thing is that no column ends on the same
row, so I can't specifiy specific cells..

Need some help to develop an average formula to pick the last five
entries
in a column.

TIA.

Bob

Averaging last 5 cells in a column
 

Try:

=AVERAGE(OFFSET(INDIRECT("B"&MATCH(LOOKUP(99^99,B: B),B:B,0)),-4,0,5))

"Bob Smith" wrote:

Thanks so much for that timely reply Toppers :).

I'm doing it with column B, so I modified your formula to
=AVERAGE(OFFSET(INDIRECT("B" & COUNTA(B:B)),-4,0,5)) . It does an average,
but not the right average. I pull down this data from a web query and cells
B1 & B2 are empty, and cell B3 is the title for the column.

Any help on modifying the formula is appreciated.

Regards, Bob


"Toppers" wrote in message
...
=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-4,0,5))

if there are no blank cells in the column

"Bob Smith" wrote:

Hi folks,

In my workbook, I have a bunch of worsheets, where I'm average all
numbers
in one column. I'd like to add one more stat, to where I'd like to
average
the last 5 #'s in a column. Only thing is that no column ends on the same
row, so I can't specifiy specific cells..

Need some help to develop an average formula to pick the last five
entries
in a column.

TIA.

Bob

Averaging last 5 cells in a column
 

Thanks Toppers. Works great ... :)

Bob

"Toppers" wrote in message
...
Try:

=AVERAGE(OFFSET(INDIRECT("B"&MATCH(LOOKUP(99^99,B: B),B:B,0)),-4,0,5))

"Bob Smith" wrote:

Thanks so much for that timely reply Toppers :).

I'm doing it with column B, so I modified your formula to
=AVERAGE(OFFSET(INDIRECT("B" & COUNTA(B:B)),-4,0,5)) . It does an
average,
but not the right average. I pull down this data from a web query and
cells
B1 & B2 are empty, and cell B3 is the title for the column.

Any help on modifying the formula is appreciated.

Regards, Bob


"Toppers" wrote in message
...
=AVERAGE(OFFSET(INDIRECT("A" & COUNTA(A:A)),-4,0,5))

if there are no blank cells in the column

"Bob Smith" wrote:

Hi folks,

In my workbook, I have a bunch of worsheets, where I'm average all
numbers
in one column. I'd like to add one more stat, to where I'd like to
average
the last 5 #'s in a column. Only thing is that no column ends on the
same
row, so I can't specifiy specific cells..

Need some help to develop an average formula to pick the last five
entries
in a column.

TIA.

Bob