Excel help needed

[brassmonkey001]

How do I calculate a rolling average?
I have a document with several worksheets within it, each representing a year and I want to put 'average to date' on each sheet so I need to take data from all the preceding sheets and put them all together and work out the average.
Each sheet has an average per month cell but I can't figure out how to write the correct formula for a rolling average.
I hope this makes sense.

 

[Garryc]

...suggest you could take and average for each sheet and take an average of the averages so to speak or,

....simple take average of all the data you have.

...use the formula wizard, and the avg forumla, you can add mulitple data ranges...

...hope it make sense...

MS office homepage is good place to get examples....

 

[Clive]

Assuming each sheet follows the same format i.e. the values to be averaged are in the same cell on each sheet you could use a 3D reference
e.g. on Sheet4 cell would be =AVERAGE(Sheet1:Sheet3!A1)

or repeat the calc on each sheet e.g.
on Sheet2 cell would be =(Sheet2!A1+Sheet1!A1)/2
on Sheet3 cell would be =(Sheet3!A1+Sheet2!A1+Sheet1!A1)/3

 

[Red Rock]

NOt sure of the formula, But if you plot all the values on a line chart, then select the line on the graph (right click) it gives the option to add a trendline, one of which is a moving average..

 

[GSmonkey]

All you need to do is do an average of all the previous yearly averages and that's your average to date.

So put a yearly average on all the sheets i.e an average of the monthly averages. The average to date is then an average of that year + all previous yearly averages.

 

[brassmonkey001]

Assuming each sheet follows the same format i.e. the values to be averaged are in the same cell on each sheet you could use a 3D reference
e.g. on Sheet4 cell would be =AVERAGE(Sheet1:Sheet3!A1)

or repeat the calc on each sheet e.g.
on Sheet2 cell would be =(Sheet2!A1+Sheet1!A1)/2
on Sheet3 cell would be =(Sheet3!A1+Sheet2!A1+Sheet1!A1)/3

Not quite. What I have is a list of people and how many times they have performed a certain task. Obviously some people go and some people are added so I can't always use the same cells over the sheets.
I'm currently using the formula =AVERAGE(B4:M4,'2007'!B4:M4,'2006'!B4:J4) but I don't really want to have to add in a range for each year if possible i.e. =AVERAGE(B4:M4,'2009'!B4:G4,'2008'!B4:M4,'2007'!B4:M4,'2006'!B4:J4). I would rather just add in cells of results i.e. the average of (the average per month of 2005, 2006, 2007 etc.) If you get what I mean.
So for instance for 2007 worksheet rolling average =AVERAGE(B4:M4, '2006'!R4), and for 2006 worksheet rolling average =AVERAGE(B4:M4, '2005'!R4) so it just picks it up each time. Unfortunately this formula doesn't give a true result.

 

[haydw]

Depends which version of Excel you are using- I have got excel 2003 at work and 2007 at home. Below is what I remember of installing in 2003.

In each version you need to install the data analysis toolpack- this isnt normally installed as standard.
Go to 'Tools'
'Add-ins'
Select the analysis toolpack and install- you may need the install CD for this depending on how it was set up.

Once it is installed you can select the data analysis tools- in with the ones for statistical functions you have never heard of there is one for moving average. You can select on which cell you start, and how many cells you want to have the moving average over eg over 2 weeks, 3 weeks or 4 weeks.

I have used this once or twice and it has come in very handy and does what it says on the tin.

 

[GSmonkey]

So for instance for 2007 worksheet rolling average =AVERAGE(B4:M4, '2006'!R4), and for 2006 worksheet rolling average =AVERAGE(B4:M4, '2005'!R4) so it just picks it up each time. Unfortunately this formula doesn't give a true result.

It is giving the result your asking for, but not the one you want.

To get the rolling average for the year you must use the average function on the YEARLY AVERAGE of the preceding years. Assuming you have data for 2004 to 2007, the average for 2006 = AVERAGE(2004 yearly average, 2005 yearly average, 2006 yearly average) NOT AVERAGE(2006 yearly average, rolling average to 2005).

Don't forget average (mean) = sum of data / number of samples. If you just use the excel function to do an average of 2006 + rolling average to previous year it assumes only 2 samples of data which is incorrect how you are applying it above.

 

[Clive]

Obviously some people go and some people are added so I can't always use the same cells over the sheets.
I'm currently using the formula =AVERAGE(B4:M4,'2007'!B4:M4,'2006'!B4:J4) but I don't really want to have to add in a range for each year if possible i.e. =AVERAGE(B4:M4,'2009'!B4:G4,'2008'!B4:M4,'2007'!B4:M4,'2006'!B4:J4).

Can't see any easy way around this.
Maybe you could include a SUM of values on each year sheet that is in a consistent cell reference and a "count" of cells with values in them (with an IIF) - then you ought to be able to calc the average across a range of sheets referencing the same cell each time (similar to my first post)

 

[talonfs]

average

1) highlight the range in the first sheet and define a name (avg2004) for this range of cells(include a blank cell at thebottom in case you want to insert others)
2) repeat this for the other sheets (avg2005, avg2006, etc)

then use the formula =AVERAGE(avg2004,avg2005,avg2006)

Pm me with your e-mail address and i will send you an example

Simon.

 

[Bigtwin]

Not quite. What I have is a list of people and how many times they have performed a certain task. Obviously some people go and some people are added so I can't always use the same cells over the sheets.
I'm currently using the formula =AVERAGE(B4:M4,'2007'!B4:M4,'2006'!B4:J4) but I don't really want to have to add in a range for each year if possible i.e. =AVERAGE(B4:M4,'2009'!B4:G4,'2008'!B4:M4,'2007'!B4:M4,'2006'!B4:J4). I would rather just add in cells of results i.e. the average of (the average per month of 2005, 2006, 2007 etc.) If you get what I mean.
So for instance for 2007 worksheet rolling average =AVERAGE(B4:M4, '2006'!R4), and for 2006 worksheet rolling average =AVERAGE(B4:M4, '2005'!R4) so it just picks it up each time. Unfortunately this formula doesn't give a true result.

Ahhh. I see where you are going wrong. You are using the wrong element of Office. Start Word. Type "on average, your performance is a disgrace". Then go to "print", then "stationary cupboard>envelope", then "out tray". Then relax.

 

[bowser]

averages

What average do you want?

2, 5, 9, 14, 26, 55, 59

add all together divide by 7, average 24.2

howver equaly valid average would be 28.5 that being me mid point between 2 and 59.

equally 14 would be correct it being the number in the mid point of the sequence

equally you can do the above having first droped off the highest and lowest in the sequence first.

they are all valid averages and excel will work out all but you have to tell it which type of average you want to use.

 

[brassmonkey001]

1) highlight the range in the first sheet and define a name (avg2004) for this range of cells(include a blank cell at thebottom in case you want to insert others)
2) repeat this for the other sheets (avg2005, avg2006, etc)

then use the formula =AVERAGE(avg2004,avg2005,avg2006)

Pm me with your e-mail address and i will send you an example

Simon.

PMd you

 

[brassmonkey001]

Ahhh. I see where you are going wrong. You are using the wrong element of Office. Start Word. Type "on average, your performance is a disgrace". Then go to "print", then "stationary cupboard>envelope", then "out tray". Then relax.

Tempting, but not the answer.

 

[brassmonkey001]

What average do you want?

2, 5, 9, 14, 26, 55, 59

add all together divide by 7, average 24.2

howver equaly valid average would be 28.5 that being me mid point between 2 and 59.

equally 14 would be correct it being the number in the mid point of the sequence

equally you can do the above having first droped off the highest and lowest in the sequence first.

they are all valid averages and excel will work out all but you have to tell it which type of average you want to use.

I'm after the mean average. I want average tasks completed per month in 2004; average tasks completed per month over 2004 & 2005; average tasks completed per month over 2004, 2005 & 2006 etc. so I can gauge trends.

Unfortunately I don't appear to have Excel on this computer! I was sure it's got Office installed but apparently not. So I guess I can't do a bit of work at home and will have to wait until tomorrow to try some suggestions. Boo fecking hoo.

 

[Yoda]

I'm after the mean average. I want average tasks completed per month in 2004; average tasks completed per month over 2004 & 2005; average tasks completed per month over 2004, 2005 & 2006 etc. so I can gauge trends.

Unfortunately I don't appear to have Excel on this computer! I was sure it's got Office installed but apparently not. So I guess I can't do a bit of work at home and will have to wait until tomorrow to try some suggestions. Boo fecking hoo.

A better trend indicator would be a "rolling 12 month average".

(ie for each month, calculate the number of tasks completed over the previous 12 months, divided by 12)

That gives the management bods a better feel for how things have progressed.

Makes for a nice graph with lots of points and (hopefully) a clear trend.