Average of absolute values of moving ranges
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)
"Amy" wrote:
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
|