# Rolling 24-month Averaging

Sep 30, 2009
One of our govt. agencies requires that I compute the 24-month rolling averages of the daily average input to a manufacturing process. In this instance, the agency is demanding the rolling average since 2003, so there are a fair number of data points involved. The process control computer can provide me the daily data (attached file contains 30 months of data), but cannot compute the 24-month rolling average.

View 4 Replies
ADVERTISEMENT
Jul 31, 2014

I would like to set the automatic rolling month when i select month at dropdown list and this will show at C5 and need to cell at the right hand side auto change month to Jul, Aug, ... Dec, Jan, Feb.

and at left handside show the previous month.

Rolling month.xlsx

View 2 Replies
View Related
Mar 16, 2010

See Attached.

The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.

At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.

Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?

View 6 Replies
View Related
Jul 2, 2008

I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.

I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.

View 9 Replies
View Related
Feb 17, 2009

In a worksheet, I have the following columns:

A: Name

B: Absence Type

D: Date

F: Event

"A" is the employee name. "B" explains the type of absence, (SICK, LATE, UNAUTHORISED, UNPLANNED). "F" states if the absence is the first date of the instance (TRUE) or a subsequent day of the same instance (FALSE).

In order to effectively monitor absence levels, I'm looking to calculate a rolling % of each absence type.

I will require a column "L" that tallies the number of total number of "TRUE" instances for each absence type, per person, within the last 12 months. A column "M" that tallies the total number of instances of an absence type, per person, within the last 12 months. And finally, based on column "M", the % of that absence type within the last 12 months.

Regarding that final column, I have been advised that to obtain this value the basis of the calculation would be "(Number of Instances / 220) * 100". I have been told 220 represents the approximate number of days an employee should work over a twelve month period, but I am dubious about the accuracy of this calculation and any alterntives.

View 9 Replies
View Related
Aug 15, 2008

I've been working on this for a couple hours and I am close to getting it how I want. Basically I want to have a rolling 15 month graph that will change according to the month you choose in the drop down tab. Example: I choose july 2007, the graph will show data from july 2007 to September 2008. I have the graph functioning, however my formulas are off (collecting the wrong data). I've included a spread sheet

View 2 Replies
View Related
Jan 30, 2013

In a column (Column "A"), I have dates (MM / dd / yyyy)

In column "B" I have valuesâ€‹â€‹.

What is the average December and January etc?

Date

Value

Average

1/12/2012

10

12

[Code] ........

View 6 Replies
View Related
Jul 14, 2014

Trying to set up a 12 month rolling sum. However, the only issue with it is that there a re future date cells already in place (august through December of this year). Is there a way to have a formula automatically ignore these by using todays date or something? That was the person using the workbook doesn't have to delete these columns and re-add in the future?

View 1 Replies
View Related
Jan 14, 2014

I've got daily water level data that spans around one hundred years. I am looking for monthly average water level data, but rather than using the whole month, I'd like to average the first two days and the last two days. I want to have a single value for each month in the 100 years of data.

If the data was smaller I could do this by hand, however having 100 years worth of data = 1200 months = so many days...

It's tough since months don't have a set number of days, and then leap years and it all gets so complicated. I've never really had a problem where date was a criteria to pull values from, and I don't know how to work with this. The internet suggest the Averageif function which I have dabbled with, but no dice.

View 1 Replies
View Related
Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.

Actual Rolling 12 Goal

Sep 2012 5,367.24 73,663.30

Oct 2012 5,649.93 69,496.28

Nov 2012 14,163.38 73,451.30 [code]....

View 6 Replies
View Related
Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)

- $25K-100K, recognize in two equal parts in months N + 1 and N + 2

- over $100K, recognize in three equal parts over 3 months

N + 1, N + 2, N + 3 ...

View 4 Replies
View Related
Jul 30, 2013

I have a spreadsheet which has data in the format of:

Date

Sales

Monday, 8 July 2013

191

Tuesday, 9 July 2013

180

[Code] ....

I calculate the average of the last four weekdays individually with =Average(A1,A8,A14..) in a cell above them to get a four week average.

Each time I add a new weeks worth of data, I manually adjust the formula to remove the first cell, and include the latest cell with new data.

I'm looking to find out if there is a way I can automate adjusting the formula so it always grabs the last four Mondays (for example)? Either in VBA or a formula.

View 3 Replies
View Related
Jun 5, 2007

I am working on a spread sheet and am trying to use rolling averages of the last 3 months. I am using worksheets for each month with a final worksheet containing the 3 mo. rolling average. I tried using a formula with OFFSET and it worked fine if all the data was in 1 worksheet, but did not work for multiple worksheets averaging to another worksheet in the same workbook.

View 12 Replies
View Related
Apr 19, 2009

Could you please supply a formula that will calculate a four week rolling average of column B in relation to the dates in column A? I tried using the average and offset functions but can't quite get it to calculate the correct averages.

View 9 Replies
View Related
Dec 30, 2009

Is there a way to make a VBA code where everytime I run this macro it adds 1 to a cell and keeps a running total?

Example:

E17 currently = 0

Running the macro makes it display 1

Running it again makes it display 2 etc...

View 2 Replies
View Related
Feb 12, 2009

I have a column of data (Column A) and I'd like to build first the Average of the first 20 data points (like in an Excel spreadsheet, cell B21 will contain the average(A2:A21), cell B22 will contain average(A3:A22), and so on... rolling).

I looked up on the internet, but I simply don't know how to start with, I mean, how to name the new array and make it rolling.

Then I will need to calculate STDEV for the same interval, and then Zscore ((Value - Average)/STDEV), but I guess it comes automatic once you made the first part.

View 9 Replies
View Related
Mar 25, 2009

I'm looking for some help to create a rolling 12 mth average. To explain better I have added a copy of the part of the sheet to help you understand what I am working with.

What I'm looking for is the 12 mth average to show up in cell c26. I have the mths listed in cell A2-A25 and the data I want averaged is in C2-C25

An additional problems is that I only want the 12 mth avg calculated if data in the next mth is greater than zero. Meaning since this month is Mar-09 (which is already collecting data) I dont want to include Mar-09 data yet. I only want to know Mar-08 to Feb-09 average. Once data is returned to Apr-09 then I want the average to show for Apr-08 to Mar-09 and so on. I dont what is better to use vba code or a formula which ever is easier.

Or if someone has a better idea please share.

PalletsTotal Orders PickedJan-083547Feb-083031Mar-083616Apr-083472May-083562Jun-083850Jul-083293Aug-082753Sep-083821Oct-083269Nov-082920Dec-083203Jan-093072Feb-093275Mar-092902Apr-090May-090Jun-090Jul-090Aug-090Sep-090Oct-090Nov-090Dec-090Average3342TL ErrorsPercentage

View 9 Replies
View Related
Aug 13, 2009

I need a formula to give me a ROLLING SUM of the last 12 entries in a column, to give a rolling year sum.

Each month a new number would be added to the column at the end just before the rolling sum calculation.

The formula then would sum the new entry plus the preceding 11 entries.

E.g. Cell values as follows: A1=10, A2=6, A3=3, A4=9, A5=4, A6=5, A7=13, A8=2, A9=7, A10=1, A11=14, A12=5

Say the sum is in cell A13, the sum=79.

A row is inserted above A13 and 7 is entered.

Now the sum calculation is in cell A14 and the calculation is from A2:A13 which equals 76.

View 9 Replies
View Related
Aug 15, 2014

I have data that is linked and is updated automatically. I am trying to use named ranges for the rolling data for each area and my date range is 5 cells using the Today()-5,4,3,2,1. The dates are rolling fine, but I cannot get the data to update, it stays the same from day to day.

look at this and see what my error is? cookies and kudos(no password on the unprotect)

View 2 Replies
View Related
Feb 5, 2014

[URL]

Alas I need to put more dynamics and fill this data set from other sheets, so I'm putting formula into column A, and this force chart react and insert another category.

In other words I want to have this fill only if data present on different tab(worksheet).

That what I have now:

[Code] ........

COUNTA

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

View 2 Replies
View Related
Mar 4, 2014

how to get the 12 months rolling ppm in excel, i have tried few methods which is not accurate,

View 1 Replies
View Related
Aug 27, 2008

I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.

I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).

Is it possible to do this?

I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.

View 9 Replies
View Related
Oct 8, 2009

I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:

...

C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...

E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...

G9 111568 298021 12940 31645 181797 36996 ... ....

...

I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?

Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly

View 10 Replies
View Related
Aug 18, 2009

Whenever I add new data I need two cells of the old data to not be counted. This should continue until old data isn’t being counted anymore.

EXAMPLE:

New Data entered Cell C36:E36 (148, 147, 214)

Old data shouldn’t be counted anymore from Cell C67:E68 (137, 152, 156, 94, 117, 158)

Total before New data Entered: 14066

After New data Entered: 13761

This formula is required for a total of 16 weeks or until all the old data isn't being calculated anymore in my rolling total.

I only need one total the three examples are just showing what the total should be calculated.

Sheet1ABCDEFGHIJKL3163253343433521472142103611481472141481472143732192168158192168158192168158383115417113715417113715417113739301771141681771141681771141684029114145164114145164114145164412816816615316816615316816615342272071441512071441512071441515217136160143136160143136160143531615710516815710516815710516854151031201661031201661031201665514144133153144133153144133153561313016013313016013313016013357121711531411711531411711531415811119157186119157186119157186591012111312612111312612111312660913615715213615715213615715261812118813412118813412118813462711712715411712715411712715463613913718213913718213913718264515213615115213615115213615165419313814719313814719313814766319420814719420814719420814767213715215613715215613715215668194117158941171589411715869Examples140661376113305Excel 2007

View 9 Replies
View Related
Sep 2, 2009

I need to create a Sum of numbers month to date Rolling. Meaning... if I have a expected number for the month, I need to be able to sum the processed numbers for each day up to the current day excluding any day after the current day.

So the formula would need to sumif the total month numbers UP TO the current day and compare that against the processed UP TO the current day.

Month Expected Processed

April 1 500 450

April 2 500 450

April 3 400 425

April 4 400 450

April 5 400

April 6 450

Month Total

sumif(Month range, month, processed)

View 9 Replies
View Related
Sep 22, 2009

What it is, I have a rolling report for the whole year, each with a score for the week.

However, it needs to show the whole year, but I need a seperate box with a summary of only the last 4 weeks on a rolling basis, ideally this would automatically update depending on the week.

View 9 Replies
View Related
Jan 24, 2007

See attached for example of what I am trying to achieve. I need a macro which will allow me within excel to roll down within column A a block of the same number for 25 rows, insert a blank row, and then add one to the starting number roll it down 25 rows insert a blank row and so on. I would like to start with the number 031400 and continue until 064300 the leading and trailing zeros are key so I have made column A text field.

View 3 Replies
View Related
Mar 28, 2007

My employer uses a scoring system to give a value to an individual’s sick record in the current rolling year.

Each person, has recorded, the number of sickness occasions and the total number of days in this rolling year period, these details go onto the final sheet where a score is calculated.

At present to get an accurate period, I have needed manually to deduct days to produce the correct time frame, ie 27/3/06 to 28/3/07 or when the sikness carrys on into the folling month.

I have tried without success to modify the rolling year formula, which as it is, will include the whole of March 06.

The formula is

=SUMIF(B5:D49,">="& DATE(YEAR(TODAY()),MONTH(TODAY())-12,1),L5:L49)

View 9 Replies
View Related
May 27, 2009

How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.

View 6 Replies
View Related
Jun 25, 2014

formula to input to excel to get the dates rolling down in A cell i want it to go A1(1/1/2014) A2(1/1/2014) then a3(2/1/2014) a4(2/1/2014) a5(3/1/2014) a6(3/1/2014) i have a lot of these dates to put into excel

View 5 Replies
View Related