If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 news =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 |
#4
|
|||
|
|||
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 news =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 |
#5
|
|||
|
|||
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 news =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 |
Thread Tools | |
Display Modes | |
|
|