Results 1 to 6 of 6
Thread: Averaging by N (97,2000)

20030623, 15:43 #1
 Join Date
 Jan 2001
 Location
 Easley, South Carolina, USA
 Posts
 45
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20030623, 16:00 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030623, 16:25 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Averaging by N (97,2000)
This
=AVERAGE(INDIRECT("A"&ROW()&":A"&(ROW()+$D$11)))
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

20030623, 16:50 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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>

20030623, 16:51 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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$11)))
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.

20030623, 22:07 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Averaging by N (97,2000)
Using nonvolatile 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.Microsoft MVP  Excel