Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example, in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see if
Excel would eventually catch on to the pattern, but it does not. There must
be a way to do this!

Thanks!

=AVERAGE(OFFSET($A$1,(ROW(A1)-1)*6,0,5,1))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"M.G." wrote in message
...
Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example,
in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see
if
Excel would eventually catch on to the pattern, but it does not. There
must
be a way to do this!

Thanks!

Sort of.....for a one time construction and if you don't want to use
volatile formulas

Try something like this:

Enter =AVERAGE(A1:A5) in the first cell
Select THAT cell AND the 4 empty cells below it
Drag the fill handle down
(Excel will creat the correct formula in every 5th cell...followed by blanks)

Then with the list of formulas and blanks still selected
Press the [F5] key....shortcut for <edit<go to
Click the [special] button...check: Blanks....click [OK]

<edit<deleteShift cells up

Done.

Something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"M.G." wrote:

Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example, in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see if
Excel would eventually catch on to the pattern, but it does not. There must
be a way to do this!

Thanks!

in the second one and copied down
=AVERAGE(INDIRECT("A"&ROW(A1)*6&":A"&ROW(A1)*10))

--
Don Guillett
SalesAid Software

"M.G." wrote in message
...
Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example,
in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see
if
Excel would eventually catch on to the pattern, but it does not. There
must
be a way to do this!

Thanks!

Ron,

That works perfectly! Thanks for your help,

Mat

"Ron Coderre" wrote:

Sort of.....for a one time construction and if you don't want to use
volatile formulas

Try something like this:

Enter =AVERAGE(A1:A5) in the first cell
Select THAT cell AND the 4 empty cells below it
Drag the fill handle down
(Excel will creat the correct formula in every 5th cell...followed by blanks)

Then with the list of formulas and blanks still selected
Press the [F5] key....shortcut for <edit<go to
Click the [special] button...check: Blanks....click [OK]

<edit<deleteShift cells up

Done.

Something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"M.G." wrote:

Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example, in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see if
Excel would eventually catch on to the pattern, but it does not. There must
be a way to do this!

Thanks!

Thanks so much for the feedback.....I'm glad I could help

***********
Regards,
Ron

XL2002, WinXP


"M.G." wrote:

Ron,

That works perfectly! Thanks for your help,

Mat

"Ron Coderre" wrote:

Sort of.....for a one time construction and if you don't want to use
volatile formulas

Try something like this:

Enter =AVERAGE(A1:A5) in the first cell
Select THAT cell AND the 4 empty cells below it
Drag the fill handle down
(Excel will creat the correct formula in every 5th cell...followed by blanks)

Then with the list of formulas and blanks still selected
Press the [F5] key....shortcut for <edit<go to
Click the [special] button...check: Blanks....click [OK]

<edit<deleteShift cells up

Done.

Something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"M.G." wrote:

Hi,

I'm pulling my hair out over this one. I want to calculate block averages.
For instance:

A1 AVERAGE(A1:A5)
A2 AVERAGE(A6:A10)
A3 AVERAGE(A11:A15)
etc....

The problem is that I cannot get Excel to recognize the pattern when I try
to pull down the column and populate the rest of the cells. For example, in
the 4th cell down, Excel will fill in "AVERAGE(A12:A16)" when I want
"AVERAGE(A16:A20)". I tried manually typing in more formulas below to see if
Excel would eventually catch on to the pattern, but it does not. There must
be a way to do this!

Thanks!