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

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

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
(E-Mail Removed)
"Robby" <(E-Mail Removed)> wrote in message
news:4B0D37CC-7F80-4F8B-B5FB-(E-Mail Removed)...
>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!!
>
>

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 Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "Robby" <(E-Mail Removed)> wrote in message
> news:4B0D37CC-7F80-4F8B-B5FB-(E-Mail Removed)...
>>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,
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!!
> >
> >

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
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> > (E-Mail Removed)
> > "Robby" <(E-Mail Removed)> wrote in message
> > news:4B0D37CC-7F80-4F8B-B5FB-(E-Mail Removed)...
> >>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!!
> >>
> >>
> >
>
>

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