Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
I am trying to figure out how to automatically average the last 4 columns in
a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Assumes there will *always* be at least 4 values entered in the range and
the data is in a contiguous block (no empty cells within the range). =AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4)) -- Biff Microsoft Excel MVP "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Hi,
No it doesn't need to be a macro, try this =AVERAGE(INDIRECT("sheet2!A"&MATCH(6.0222*10^23,Sh eet2!A:A)&":E"&MATCH(6.0222*10^23,Sheet2!A:A))) Mike "Greg" wrote: I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Ah,
I misread your post but it still doesn't need to be a macro =AVERAGE(OFFSET(INDEX(Sheet2!5:5,,COUNT(Sheet2!5:5 )),,-4,1,5)) Mike "Mike H" wrote: Hi, No it doesn't need to be a macro, try this =AVERAGE(INDIRECT("sheet2!A"&MATCH(6.0222*10^23,Sh eet2!A:A)&":E"&MATCH(6.0222*10^23,Sheet2!A:A))) Mike "Greg" wrote: I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Biff and Mike:
Thanks for the help! You're great! The cells should have data, if not, does it grab data from the last 4 data points? Also, does it matter if the data is Zero? (Not null, but 0) Thanks again! Greg "T. Valko" wrote: Assumes there will *always* be at least 4 values entered in the range and the data is in a contiguous block (no empty cells within the range). =AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4)) -- Biff Microsoft Excel MVP "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
This *array* formula will average the *last* 4 entries, even with blanks
in-between, AND, will average any available values if less then 4 are entered. So, at the beginning of the year, where there's less then 4 weeks data, OR, if you're closed for 2 weeks vacation with no data, this might be a viable formula to try. Say your range is sized for 52 weeks: =AVERAGE(INDEX(A5:AZ5,LARGE(COLUMN(A5:AZ5)*(A5:AZ5 <""),4)):AZ5) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Glad that helped,
If you want to use the last 4 populated cells and ignore zero try this =AVERAGE(INDEX(5:5,LARGE(COLUMN(5:5)*(5:5<0),5)): IV5) I've done it for the active sheet but you should be able to convert it for sheet 2 Mike "Greg" wrote: Biff and Mike: Thanks for the help! You're great! The cells should have data, if not, does it grab data from the last 4 data points? Also, does it matter if the data is Zero? (Not null, but 0) Thanks again! Greg "T. Valko" wrote: Assumes there will *always* be at least 4 values entered in the range and the data is in a contiguous block (no empty cells within the range). =AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4)) -- Biff Microsoft Excel MVP "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Hi Mike.
Thank you for the formula, upon data checking, there's a problem. It doesn't return the correct number. Here's the formula I'm using and the data: =AVERAGE(OFFSET(INDEX('Prior Weeks'!6:6,,COUNT('Prior Weeks'!6:6)),,-4,1,5)) Calls Received/Daily Avg 642.75 591.20 563.80 607.40 590.00 604.25 662.20 530.80 The number returned should be 596.8125, but with the formula, it returns 605.530. Any ideas? TIA, Greg "Mike H" wrote: Ah, I misread your post but it still doesn't need to be a macro =AVERAGE(OFFSET(INDEX(Sheet2!5:5,,COUNT(Sheet2!5:5 )),,-4,1,5)) Mike "Mike H" wrote: Hi, No it doesn't need to be a macro, try this =AVERAGE(INDIRECT("sheet2!A"&MATCH(6.0222*10^23,Sh eet2!A:A)&":E"&MATCH(6.0222*10^23,Sheet2!A:A))) Mike "Greg" wrote: I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Forget it, I used a different formula and it worked:
=AVERAGE(OFFSET('Prior Weeks'!B7,,COUNT('Prior Weeks'!7:7)-1,,-4)) "Greg" wrote: Hi Mike. Thank you for the formula, upon data checking, there's a problem. It doesn't return the correct number. Here's the formula I'm using and the data: =AVERAGE(OFFSET(INDEX('Prior Weeks'!6:6,,COUNT('Prior Weeks'!6:6)),,-4,1,5)) Calls Received/Daily Avg 642.75 591.20 563.80 607.40 590.00 604.25 662.20 530.80 The number returned should be 596.8125, but with the formula, it returns 605.530. Any ideas? TIA, Greg "Mike H" wrote: Ah, I misread your post but it still doesn't need to be a macro =AVERAGE(OFFSET(INDEX(Sheet2!5:5,,COUNT(Sheet2!5:5 )),,-4,1,5)) Mike "Mike H" wrote: Hi, No it doesn't need to be a macro, try this =AVERAGE(INDIRECT("sheet2!A"&MATCH(6.0222*10^23,Sh eet2!A:A)&":E"&MATCH(6.0222*10^23,Sheet2!A:A))) Mike "Greg" wrote: I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Hi Biff.
Thank you for the formula. Unfortunately, now the scope has changed. Instead of a 4 week average, they want the previous 4 weeks individually (last week, 2 weeks ago, 3 weeks ago, 4 weeks ago) I couldn't follow the formula you created to know where to change numbers. Since I know it's possible to pull an average over the 4 columns, I assume it's also possible to just designate the proper columns. But, that's where I need help. TIA, Greg "T. Valko" wrote: Assumes there will *always* be at least 4 values entered in the range and the data is in a contiguous block (no empty cells within the range). =AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4)) -- Biff Microsoft Excel MVP "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically average the last 4 columns
Forget it.
4 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-4,,-1) 3 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-3,,-1) 2 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-2,,-1) 1 weeks prior =OFFSET('Prior Weeks'!$B$6,,COUNT('Prior Weeks'!6:6)-1,,-1) "Greg" wrote: Hi Biff. Thank you for the formula. Unfortunately, now the scope has changed. Instead of a 4 week average, they want the previous 4 weeks individually (last week, 2 weeks ago, 3 weeks ago, 4 weeks ago) I couldn't follow the formula you created to know where to change numbers. Since I know it's possible to pull an average over the 4 columns, I assume it's also possible to just designate the proper columns. But, that's where I need help. TIA, Greg "T. Valko" wrote: Assumes there will *always* be at least 4 values entered in the range and the data is in a contiguous block (no empty cells within the range). =AVERAGE(OFFSET(Sheet2!A5,,COUNT(Sheet2!5:5)-1,,-4)) -- Biff Microsoft Excel MVP "Greg" wrote in message ... I am trying to figure out how to automatically average the last 4 columns in a worksheet that every week adds a column. In other words, for "Prior4Weeks" on Sheet1, the formula would be =AVERAGE('Sheet2'!A5:D5) But then, a week later with another week's data summarized in a column added to Sheet2, the formula should read be =AVERAGE('Sheet2'!B5:E5) I know it's gotta be a macro, but don't know where to start. TIA, Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
How to change average range automatically? | Excel Discussion (Misc queries) | |||
Adding an Average line (from data sheet) to a chart automatically. | Charts and Charting in Excel |