Create dynamic rolling 3 month data that increases in one month increments

Hi All, I have a very large data set of survey responses. We report the result as a rolling 3 month period that increases at a 1 month interval.

For example:
Jan - Mar
Feb - Apr
Mar - May
Apr - Jun
May - Jul and so on....

At the moment I use a pivot table to get the monthly result. I then have to select the 3 month period I want and add it to a separate table, I then have a chart based on the table data. Is there a formula that I can add to the raw data that would automatically update the date ranges so I can plot a chart directly from the pivot table?

PS: I've never used VBA before... Hope this makes sense!

Hi,

From your description, what you are asking for sounds achievable. Unfortunately, there isn't a generic formula for such an unknown situation.
You would need to upload a file so that the requested process could be evaluated and a solution provided.

Recommend you upload a "working" copy of your file. Sanitized if need be.

Cheers

Personally I avoid pivot tables wherever I can. I won't bore you with the details of why, it's just personal preference.

You could set up your own 'table' then add a data validation drop down to select the first month (best if VBA is not an option) or even a SpinButton control to be able to jump up and down a month at the click of a button (if VBA is an option).

Both approaches are simple to set up with a few formulas. The latter would require minimal VBA to make it work (about 6 lines in total).

Perhaps if you could post some sample data so we can see the layout we can go from there.

BSB

Here's a suggestion, attached.

It sums the report month + the two preceding months.

Hi All,

Thanks for your suggestions so far... what I didn't mention that may complicate things is this needs to work out the NPS result, it's not as simple as just averaging
3 months totals or adding them up.

I have attached a dummy sample of raw data (raw data tab). An example of what someone internally has done for me (example tab) and a results tab where I have
copied over the example formula from the original to try and get it work with the dummy data but it won't.... I have also added a copy of what the chart needs
to look like. As you can see any given month will be in 3 different data points because it goes up one month at a time but each data point covers a 3 month period.
The chart doesn't have an end data and will continue to grow over time. However, if having a start end data is the only way to make it work I would like to be able
to see at least 12 months of data at any given time.

I know the 'example' works and it's great but I want to take away the effort of having to drag the cells across every month. I just want to have to update the raw
data and then refresh everything and have it all update on it's own.

Attached will help you visual it hopefully - thanks again!