Dynamic Average Formula across multiple worksheets

Hello Everyone

I have searched the forum for the better half of the day, with no resolve. So if there is an answer out there, I apologize for wasting everyone's time.

If you take a look at the workbook "Running Average" on the "Dec 16" Worksheet "Cell C56". I have this formula:
=AVERAGE('Apr 16:Dec 16'!M34), which works great. I know that Cell M34 looks empty, but there is a formula with a hiddne format

What I need is:

as I add a new month "Jan 17" I would like the formula to automatically change from =AVERAGE('Apr 16:Dec 16'!M34) to =AVERAGE('Apr 16:Jan 17'!M34)

I have tried to have a formula on a different worksheet and have Cell C56 reference the formula. But I end up getting the same number across all months, instead of getting the average leading up to that month.

Please let me know if anyone out there know, thanks!

Hello Wanmuhd,

Thank You for taking the time to look into this.
I have thought about that, and I think it would impact the average by lowering it. I might end up using this method, but would like to avoid it.
I need accurate numbers, people's jobs depend on it.

I don't think there is anyway of doing this without you having to change a formula.

Why not simply add in all your sheets for the year and put the AVERAGE formula in each sheet. 10 minutes (slow) typing!

I have looked at options and attach one for your interest if nothing else.

Sheet "Parameter" has a list of named ranges which define months to be averaged.

in "OCT", "NOV" and "Dec" sheets in H56

=SUMPRODUCT(SUMIF(INDIRECT("'"&Oct &"'!M34"),"<>0"))/COUNTA(Oct)

with Oct replace by Nov and Dec

JohnTopley thank you for the input,

I am just not sure what is the point of one formula over the other.If I have to change the month by hand in either situation.
Unless I am missing something. Please let me know.

Anyone else have any ideas?

I think the methods work even the simple one by wanmuhd. Test it yourself. If a sheet contains a blank cell, it is not included in the average calculation, even if the blank is the result of a formula.

What were your concerns?

Dav

or change the formula in cell m34

perhaps

if(SUM(N8:N32)>0,SUM(N8:N32),"")

So if nothing has happening in a month, it is not included in the calculations. I am assuming something takes place in the months, or you can use another trigger, based on a cell or date