Binned Averages

Posted by John Kelly on August 29, 2000 10:20 AM

I have a 2 column data set that I would like to
perform a binned average on. By binned average I'd
like to take the first N values of each column, aveage
it, then move on and take the next N to N+N values and
average them, etc. Basically, I'm doing an smoothing
on data by averaging a certain number of adjacent
points. I can't seem to make Excel do this.

AdvTHANKSance

John

Posted by Tim Francis-Wright on August 29, 0100 2:25 PM

I've assumed that you have data in columns A and B, starting in row 1 and that you want to have the binned averages in columns C and D, respectively.

Select a cell off to the side; name it BIN; and put the bin size there.

The entries in Column C will be:
IF(MOD(ROW(),BIN)=1,AVERAGE(INDIRECT("A"&ROW()&":A"&BIN+ROW())),"")

The entries in Column D will be:
IF(MOD(ROW(),BIN)=1,AVERAGE(INDIRECT("B"&ROW()&":B"&BIN+ROW())),"")

[if your data starts on another row, replace
the =1 in the formula with =(whatever the first row is)]

HTH