Copy Average Caluclations

[Robby]

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!!

 

[Tom Ogilvy]

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

 

[Don Guillett]

one way to try
=AVERAGE(OFFSET($b$1,(ROW(A1)*5)-12,0):OFFSET($b$1,(ROW(b1)*12)-1,0))

 

[Don Guillett]

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))

 

[Robby]

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!

 

[Robby]

This works PERFECT!
Thanks much!

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
(e-mail address removed)

 

[Tom Ogilvy]

I had a typo in my formula

=AVERAGE(OFFSET(Sheet5!$B$1,(ROW()-2)*12+1,0,12,1))