Averaging by N (97,2000)

Topic

New Reply

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.

Reply | Quote

Replies

Put this in B1 and copy it down the rows

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Oops, you’re right (of course)

Reply | Quote

Using non-volatile INDEX…

=IF(COUNT($A$1:A1)*NumVals1,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)*NumVals1,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.

Reply | Quote