1. Averaging by N (97,2000)

Assume that column A has a list of values in it. In column B I want a list of n averages, for instance b1=average(a1:a5), b2=average(a6:a10), etc.
Is there a simple formula that can be replicated down column B that would be generic for any value of n? I thought that OFFSET might be a
possibility but can't think of a way to implement it.

2. Re: Averaging by N (97,2000)

Put this in B1 and copy it down the rows

<pre>=AVERAGE(INDIRECT("A"&(+ROW()-1)*NumValues+1&":A"&(+ROW()*NumValues)))</pre>

The defined range "NumValues" is 5 in your example, but it can be any value>0 (it is the number of points you want to average. Change the columns as appropriate also.

Steve

3. Re: Averaging by N (97,2000)

This
=AVERAGE(INDIRECT("A"&ROW()&":A"&(ROW()+\$D\$1-1)))

will give A1:A5, A2:A6, A3:A7, ... etc

He wanted A1:A5, A6:A10, A11:A15, ... etc

=AVERAGE(INDIRECT("A"&(+ROW()-1)*\$D\$1+1&":A"&(+ROW()*\$D\$1)))

If you want a cell listed, though if you defined the cell D1 as "NumValues" you have what I listed:

=AVERAGE(INDIRECT("A"&(+ROW()-1)*NumValues+1&":A"&(+ROW()*NumValues)))

Steve

4. Re: Averaging by N (97,2000)

Oops, you're right (of course) <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

5. Re: Averaging by N (97,2000)

Ignore this post - it doesn't answer the question - sorry.

The formula you provide will fill down correctly for any value of n. But perhaps you want to specify n in a separate cell, so that you can vary it easily. Say that you put the value of n in cell D1. Put the following formula into B1:

=AVERAGE(INDIRECT("A"&ROW()&":A"&(ROW()+\$D\$1-1)))

This formula can be filled down. You can change the number of cells over which the average is calculated by changing the value in D1.

6. Re: Averaging by N (97,2000)

Using non-volatile INDEX...

=IF(COUNT(\$A\$1:A1)*NumVals<=COUNT(\$A\$1:\$A\$25),AVER AGE(INDEX(\$A\$1:\$A\$25,IF(COUNT(\$A\$1:A1)>1,NumVals*( COUNT(\$A\$1:A1)-1)+1,1),1):INDEX(\$A\$1:\$A\$25,COUNT(\$A\$1:A1)*NumVals ,1)),"")

Using volatile OFFSET...

=IF(COUNT(\$A\$1:A1)*NumVals<=COUNT(\$A\$1:\$A\$25),AVER AGE(OFFSET(A\$1,IF(COUNT(\$A\$1:A1)>1,NumVals*(COUNT( \$A\$1:A1)-1),0),0,NumVals,1)),"")

NumVals stands for blocks of "N values" that you want to average.

Both formulas should behave correctly against inserting rows before the data.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•