Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When Averaging a column, exclude value based on another cell value | Excel Worksheet Functions | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Averaging a column with 0's | Excel Worksheet Functions | |||
Automating averaging a column over time periods. | Excel Discussion (Misc queries) | |||
Averaging Cells | Excel Discussion (Misc queries) |