Indirect and Average?

[rik714z]

I believe what I need is the indirect and average formulas combined but I am not sure.

I am building a file that will give me averages based on the last 6 weeks of sales history but I want to cell references in the average formula to stay on the exact cells so I can keep adding data each week and only view the last 6 weeks average.

What do you guys think I should do? Indirect seems to function that way but I cant figure out how to get it to work.

If I use just normal average, any time i add columns to enter the new data the formula moves to the further out columns.


Basically:

A1= average of A2, A3, A4, A5, A6, A7
(and A2-A7 never changes in the formula for A1 even if I add columns)

 

[Scott Huish]

=average(indirect("a2:a7"))

 

[FDibbins]

Or maybe something like this?
=averageifS(value-range, date range,"<="&today(),date range,">="&today()-42)

 

[rik714z]

Thank you both! I had that exact formula Scott but I was missing the quotes...you live and you learn.

Thank you all.