This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Thank You For Looking...
I have a rolling years worth of data in a linked data table that I am calling "data". What I would like to calculate is a year to date average of a value.
So imagine The field that has the date in it is called [Report_Date]
The field average I want is called [Count_Total]
So lets say I have data for Nov, Dec, Jan, Feb and March (24, 27, 15, 18, 21) that would give me an average of 18 for the year to date ((15+18+21)/3)
When I insert April's data of 12 I would have (24, 27, 15, 18, 21, 12) and the year to date average would update to 16.5 ((15+18+21+12)/4)
I hope what I want to do is clear.
I am using a formula to calculate the first day of the current year (=date(year(today()),1,1) and it's cell reference is E1
I just have not been able to get the formula to calculate average to work unless directly select the fields...which I can't do since it is a rolling year and the months and cell references change.
Anyone who has any suggestions or insight I would be excited to see it. Thank you in advance for your assistance.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
How about
=AVERAGEIFS(TableName[Count_Total], TableName[Report_Date], ">="&E1, TableName[Report_Date], "<="&TODAY())
where TableName is the name of the table.
How about
=AVERAGEIFS(TableName[Count_Total], TableName[Report_Date], ">="&E1, TableName[Report_Date], "<="&TODAY())
where TableName is the name of the table.
PERFECT!!!!
I tried AVERAGEIFS, but I couldn't get it to apply the filter...but starting with criteria1, I had nothing like what you did.
You are BRILLIANT!!
Thank You