Variable averaging span question.

**Anthony01803** · 03-05-2010, 10:05 AM

Office Version:Office 2003Operating System:Windows XP

I need help manipulating cell reference numbers to average periodically grouped data. Example: Average Row 1 - 10 and put value in row 10 adjacent column, then average row 11 - 40 and put value in row 40 adjacent column. Then do this over an over again 600 times but obviously jumping to row 41 to repeat the process. My example cell equation: =average(A($1+(0x$40)):A($10+(0x$40)))
=average(A($11+(0x$40)):A($40+(0x$40)))

Where 0 is the number of periods for averaging.

BUT, this doesn't work. How can I get this process accomplished for 500,000 data points?

Thanks for your help!

**DonkeyOte** · 03-05-2010, 11:16 AM

I'm not quite sure on the following points:

1 - you say A1:A10, then A11:A40 ... so my question would be: are those 2 separate dimensions to be alternated throughout (ie 10x1, 30x1) ?

2 - you say 2003 but mention 500,000 data points... I'm guessing these points are thus spread across numerous columns ?

Assuming the 10 and 30 dimensions are to be repeated...and data points in A1 onwards with results to go into B (displayed in B10, B40, B50 etc...)

B1: =CHOOSE(MATCH(MOD(ROW(B1)-ROW(B$1),40),{0,9,10,39}),"",AVERAGE(INDEX($A:$A,ROW(B1)-9):$A1),"",AVERAGE(INDEX($A:$A,ROW(B1)-29):$A1))
copied down for all rows

**Anthony01803** · 03-05-2010, 03:44 PM

Can you please help decode what all the numbers represent in the above post?

**DonkeyOte** · 03-05-2010, 03:57 PM

MOD(ROW(B1)-ROW(B$1),40)

The MOD is used to determine where in the recurring "chunk" of 40 rows the current row resides ... this result will be 0-39
(eg Row 1 being 0, Row 10 being 9 and Row 40 being 39 and all other numbers in between)

We know that we wish to conduct Avg calcs of one sort or another every 10th (mod 9) and 40th (mod 39) row within the dataset - for all other rows we simply want to return a Null.

To work this out I opted to push the Mod return into a Match

MATCH(mod result,{0,9,10,39})

the above will return a value between 1-4 depending on where the current Mod value is placed in terms of the value within the Match inline array, eg:

00-08: 1
09: 2
10-38: 3
39: 4

We then use the above value in a CHOOSE to establish the appropriate action to take - if the MATCH returned either 1 or 3 we want to return a Null - if 2 or 4 we need to perform an avg calc, eg:

CHOOSE(match result,"",avg1,"",avg2)

The Average calcs themselves are virtually identical except for their starting points, eg:

avg1: AVERAGE(INDEX($A:$A,ROW(B1)-9):$A1)
avg2: AVERAGE(INDEX($A:$A,ROW(B1)-29):$A1)

we know avg1 range should be 10x1, ie 9 rows prior and current row

we know avg2 range should be 30x1, ie 29 rows prior and current row

So in terms of overall workflow:

1 - establish Mod of range to determine 10th and 40th rows in each block
2 - use Match to determine which action to take pending Mod return
3 - use Choose in conjunction with 2 to identify said action
4 - use Index within Average to establish the appropriate range to Average (based on which row is being calculated - ie 10th/40th)

I hope that helps.