Rolling 3 month average on dashboard

[renatria]

Getting ready for a new fiscal year, and I've been asked to add something to the monthly dashboard I give my boss with the monthly financial highlights. We have a recurring event with a band, and I need to start including the average attendance over the last three events per band. My data for this is pretty small- headers are Date, Band, Turnout, Notes. (Col A-D)

Currently, I'm doing a pivot table with the months as rows and the bands as columns with sum of turnout as the cells, which is fine so long as a band doesn't play more than once, and then manually finding the average, d

Ideally, this would catch if a band plays twice, or if they skip a month. (That is, it can't just be the sum of turnout for the most recent three months/3)

There's got to be a better way. I'm assuming someone on here will know it.

 

[DRSteele]

Please post a sample of your boss' spreadsheet so we can help you and you can help him.

Follow this to learn how to send us a spreadsheet: http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

 

[renatria]

I'm unable to add the add-in, but I've copy/pasted some sample data and pivots generated from the sample data into a public google sheets.

Ideally, I'd like to summarize the data directly onto the dashboard with a pivot table.

Actually I just had an idea, as I'm looking for the average for dates in the last three months, I should be able to filter by dates, and then just display average turnout by band.

I mean the date filter is somewhat clunky, but it'll still be better than what I've got now.