Average of absolute values of moving ranges

  • Thread starter Thread starter Amy
  • Start date Start date
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
 
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)
 
Back
Top