Average of absolute values of moving ranges

A

Amy

I'm trying to get the average of the absolute values of a set of data over 8
weeks. Each week is on a seaparate sheet so to capture the moving ranges I've
been using the formula below to get my result. Is there an easier way?

=AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week
3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week
6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2))

Thanks!
Amy
 
J

JLatham

The use of the ABS() throws a kink into it. I'd consider adding a 'helper
cell' on each of the sheets with the formula =ABS(G2) in it. For the example
below, we'll say that's in cell G3

If the weekly sheets are contiguous, then you can reference the first and
last sheets in the group to get the average, as:
=AVERAGE('Week 1:Week 8'!G3)
then a simple change would roll it forward to the next period:
=AVERAGE('Week 2:Week 9'!G3)
 
Top