Indirect and Average?

rik714z

New Member
Joined
Feb 23, 2016
Messages
2
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)
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Or maybe something like this?
=averageifS(value-range, date range,"<="&today(),date range,">="&today()-42)
 

rik714z

New Member
Joined
Feb 23, 2016
Messages
2
Thank you both! I had that exact formula Scott but I was missing the quotes...you live and you learn.

Thank you all.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top