Dynamic AVERAGE Across Multiple Worksheets

[jski21]

Good day Mr. Excel Team,

Came across an incumbent's workbook that has multiple tabs by year. Each tab has numerous accounts (50+), by month, for various revenue items. I need to quickly get an average for each account across all the worksheets. I could simply click through each, add them, and divide by the # of observations =('2010'!A1+'2011'!A1...etc.), but I will likely need to advance this forward dynamically as we go further into 2022, 2023 and beyond.

Might there be a formula to capture an average dynamically across multiple sheets in a results worksheet that would accommodate future growth in the data set?

Many thanks, as always, for your review of this post and your help.


jski

 

[SpillerBD]

"Excel is not a database."
A formula would be a monster for the multiple sheets/ranges.
You should consolidate the data into a single data model using the Get and Transform (PowerQuery) feature. You should then be able to get your Averages in a Pivot Table. I read your requirements as not needing any DAX (PowerPivot) calculations, but if necessary, your data will already be in the data model.

 

[jski21]

That's what I thought. Will give this a go SpillerBD. Thanks.