Varying =AVERAGE length

**Rbrt** · 09-13-2014, 04:23 PM

Hey Folk!
This is probably simple but I'm stumped. I am creating a rolling average for a column of numbers but I want to be able to quickly lengthen or shorten the roll. For example E26 might read: =AVERAGE(D20:D26) but I might want to quickly swap that to a 14 day roll: E26 = AVERAGE(D13:D26). And I'm going to have a number of columns (maybe 10) that I want to quickly tweak.

Maybe my question is, how can I make an equation like this work: E26 = AVERAGE(D26-x:D26) where x is the value in D1 (in this case, 7-1).

Thanks!

**shg** · 09-13-2014, 04:33 PM

=AVERAGE(INDEX(D:D, 27-D1):D26)

D1 contains the number of cells to average.

**Rbrt** · 09-13-2014, 06:22 PM

Cool, except I would need to vary the 27 also. I have a column of numbers and a column of rolling averages, see attached.

You would think I could figure it out from here. Something like this: =AVERAGE(INDEX(D:D,INDEX(A:A,A25)-D$1):D25)

Appreciate the help! Index is a cool function.

This statement hopefully is no longer valid - OK, the equations in this spd sht are slightly more complex than in the text above - Sorry!, but I can't seem to delete it and I know you can handle the extra stuff.