# Indirect and Average?

#### rik714z

##### New Member
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)

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
=average(indirect("a2:a7"))

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

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

Thank you all.

Replies
15
Views
165
Replies
3
Views
116
Replies
2
Views
159
Replies
2
Views
133
Replies
11
Views
414

1,203,756
Messages
6,057,162
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back