|
Copy Average Caluclations
I have a "data" worksheet that has about 20K + records. Col A contains the
date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
Copy Average Caluclations
in the second row of the table (assume row 2)
=Average(offset(Sheet1!B1,(row()-2)*12+2,0,12,1)) then drag fill it down the column. if the formula isn't started in row 2, you would have to adjust this part of the formula: (row()-2)*12+2 -- Regards, Tom Ogilvy "Robby" wrote: I have a "data" worksheet that has about 20K + records. Col A contains the date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
Copy Average Caluclations
one way to try
=AVERAGE(OFFSET($b$1,(ROW(A1)*5)-12,0):OFFSET($b$1,(ROW(b1)*12)-1,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Robby" wrote in message ... I have a "data" worksheet that has about 20K + records. Col A contains the date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
Copy Average Caluclations
Tom,
You are the MAN! This works great! I do have a question though... If you have time (grin) could you explain a little how the offset works? The reason that I ask is that if I use the full function below, I get a different result that what I do when I use =AVERAGE(B2:B13). Thanks again! "Tom Ogilvy" wrote: in the second row of the table (assume row 2) =Average(offset(Sheet1!B1,(row()-2)*12+2,0,12,1)) then drag fill it down the column. if the formula isn't started in row 2, you would have to adjust this part of the formula: (row()-2)*12+2 -- Regards, Tom Ogilvy "Robby" wrote: I have a "data" worksheet that has about 20K + records. Col A contains the date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
Copy Average Caluclations
This works PERFECT!
Thanks much! "Don Guillett" wrote: If your data starts in row 2 then =AVERAGE(OFFSET($b$2,(ROW(A1)*12)-12,0):OFFSET($b$2,(ROW(A1)*12)-1,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... one way to try =AVERAGE(OFFSET($b$1,(ROW(A1)*5)-12,0):OFFSET($b$1,(ROW(b1)*12)-1,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Robby" wrote in message ... I have a "data" worksheet that has about 20K + records. Col A contains the date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
Copy Average Caluclations
I had a typo in my formula
=AVERAGE(OFFSET(Sheet5!$B$1,(ROW()-2)*12+1,0,12,1)) -- Regards, Tom Ogilvy "Robby" wrote: Tom, You are the MAN! This works great! I do have a question though... If you have time (grin) could you explain a little how the offset works? The reason that I ask is that if I use the full function below, I get a different result that what I do when I use =AVERAGE(B2:B13). Thanks again! "Tom Ogilvy" wrote: in the second row of the table (assume row 2) =Average(offset(Sheet1!B1,(row()-2)*12+2,0,12,1)) then drag fill it down the column. if the formula isn't started in row 2, you would have to adjust this part of the formula: (row()-2)*12+2 -- Regards, Tom Ogilvy "Robby" wrote: I have a "data" worksheet that has about 20K + records. Col A contains the date and time and Col B contains the value: A B 1 Time Value 2 6/13/07 5:31 PM 3.5 3 6/13/07 5:35 PM 2.8 4 6/13/07 5:40 PM 1.9 and so on... I am trying to create a table that displays the average of every 12 values: =AVERAGE(B2:B13) =AVERAGE(B14:B25) and so on... The problem that I am running into is that when I try to grab the handle and copy the functions, I get: =AVERAGE(B2:B13) =AVERAGE(B3:B14) instead of: =AVERAGE(B2:B13) =AVERAGE(B14:B25) There has got to be an easy way to copy the average calc functions. My only other option is to fat finger this functions in 300+ times. Thanks all!! |
| All times are GMT +1. The time now is 09:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com