**Question description:**

*This user has given permission to use the problem statement for this blog.*

Need to calculate a rolling 4 week average in pivot table

Solved by X. B. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
01/08/2018 - 07:50

hello,

User
01/08/2018 - 07:50

I need to calculate a rolling 4 week average in a pivot table

Excelchat Expert
01/08/2018 - 07:50

Hello!

Excelchat Expert
01/08/2018 - 07:50

Welcome to got it pro!

User
01/08/2018 - 07:50

similar to the (% difference from previous week function)

User
01/08/2018 - 07:51

except it needs to be % difference from previous 4 week average

Excelchat Expert
01/08/2018 - 07:51

Sure.

User
01/08/2018 - 07:51

it needs to be within the pivot table not calculated off of the pivot table

Excelchat Expert
01/08/2018 - 07:51

Could you show me some sample data?

User
01/08/2018 - 07:51

so a calculated field of some sort within the pivot

Excelchat Expert
01/08/2018 - 07:51

Sure

Excelchat Expert
01/08/2018 - 07:52

Let's see how to do it

Excelchat Expert
01/08/2018 - 07:53

So you want the percentage of?

User
01/08/2018 - 07:53

% difference from previous 4 week rolling average

User
01/08/2018 - 07:54

It needs to either be an automatic calculation in the table or as a calculated field in the pivot

Excelchat Expert
01/08/2018 - 07:54

ok I see

User
01/08/2018 - 07:55

but this is a sample dataset

Excelchat Expert
01/08/2018 - 07:55

Sure

Excelchat Expert
01/08/2018 - 07:55

What is D5 should be in this case?

User
01/08/2018 - 07:55

my dataset is much larger and is connected by an access connection

Excelchat Expert
01/08/2018 - 07:55

Sure

Excelchat Expert
01/08/2018 - 07:56

what is the value in D5?

User
01/08/2018 - 07:57

It needs to be done through a calculated field or some type of access query

Excelchat Expert
01/08/2018 - 07:57

Ok

User
01/08/2018 - 07:57

ive been trying to figure this out for quite some time and have narrowed it down to that

Excelchat Expert
01/08/2018 - 07:57

So far, I think the best way to do this is go through this way

Excelchat Expert
01/08/2018 - 07:58

And then create another column for percentage calcualtion

User
01/08/2018 - 07:58

no

User
01/08/2018 - 07:58

i am running a pivot based off a table connection

User
01/08/2018 - 07:58

I dont have an actual table to manipulate

User
01/08/2018 - 07:59

unless its manipulating the table within access

Excelchat Expert
01/08/2018 - 07:59

Yes

Excelchat Expert
01/08/2018 - 07:59

I think that the easiest way to do so

User
01/08/2018 - 07:59

so this will be done in access?

Excelchat Expert
01/08/2018 - 08:00

Yes. it will be easier

User
01/08/2018 - 08:00

ok how

Excelchat Expert
01/08/2018 - 08:00

I have tried to use pivot table

Excelchat Expert
01/08/2018 - 08:00

But even in pivot table, it is still the save to manully inseert another column for rolling average and percentage calculation

Excelchat Expert
01/08/2018 - 08:01

So, eseentially that will be the same

User
01/08/2018 - 08:01

so how do I add the rolling average in access

Excelchat Expert
01/08/2018 - 08:02

Wait. In excel, there might be a way to do this

Excelchat Expert
01/08/2018 - 08:02

To calculate a moving average, first click the Data tab’s Data Analysis command button.

Excelchat Expert
01/08/2018 - 08:02

When Excel displays the Data Analysis dialog box, select the Moving Average item from the list and then click OK.

Excelchat Expert
01/08/2018 - 08:03

find the data that you want to use to calculate the moving average.

Excelchat Expert
01/08/2018 - 08:03

In the Interval text box, tell Excel how many values to include in the moving average calculation.

Excelchat Expert
01/08/2018 - 08:03

Then tell Excel where to place the moving average data.

Excelchat Expert
01/08/2018 - 08:03

After you finish specifying what moving average information you want calculated and where you want it placed, click OK.

User
01/08/2018 - 08:06

i cant find a data analysis button

User
01/08/2018 - 08:06

nvm

Excelchat Expert
01/08/2018 - 08:07

Can you find from your local MS excel?

User
01/08/2018 - 08:07

it doesnt allow for a dynamic average

Excelchat Expert
01/08/2018 - 08:07

is there any error msg?

User
01/08/2018 - 08:07

if I add another week to my pivot i will have to recalculate

Excelchat Expert
01/08/2018 - 08:08

can you include a larger range?

User
01/08/2018 - 08:08

no

User
01/08/2018 - 08:09

i need for it to be built in to either the pivot as a calculated field or as an access query\

User
01/08/2018 - 08:09

the solution you provided is a manual fix not an automatic calculation

Excelchat Expert
01/08/2018 - 08:10

Yes since from excel level this is the only way to do so

User
01/08/2018 - 08:10

what about access

Excelchat Expert
01/08/2018 - 08:10

If you need a automatic way to do this, you may need to consider access

Excelchat Expert
01/08/2018 - 08:10

Since this is a excel platform, we really not be able to assist with access portion

Excelchat Expert
01/08/2018 - 08:10

We do apologize for this inconvience

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*