Rolling & Average (write A Single Formula That I Can Copy Down)

Mar 2, 2009

I've got a "Date" Column which has every day of every month, and next to that several columns with different types of data to correspond to a particular date.

To simplify this (because there are TONS of dates), I've made another "Date" column that displays only every 5th day. Now what I'd like to do of course is average the corresponding data over five days.

I don't know how to write a single formula that I can copy down that will do this.

I've attached a sample spreadsheet with only one month's data, the stuff I need a formula / code for is in red

I am trying to create a formula that looks for the last populated cell, counts 12 cells back and returns an average for all of the cells in that range. I know I have done this before with some combination of INDEX and COUNT, but I can't seem to remember how I did it and my experimenting is not proving fruitful.

Attached is a simplified version of the layout I am working with

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?

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.

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.

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.

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

how to calculate a six month rolling average for hours in AD column and AE column, this average would be entered in to AF1. the date column is AC the date is not consecutive it does miss days out.

I've got a worksheet that has chronological quarterly data for which I'd like to do a 5-year rolling average. The layout is: Column A = Dates, Column B = Values, and Column C = 5-year Rolling Average. My problem is that I want to exclude zero-values from the averaging calculation. I tried the following:

What I have is a spreadsheet with a whole bunch of sporting data on it. see attachment for an idea of the layout, Stats.jpg

In these data each round a player is given a rating. What I want to do is calculate is the average rating for each player across the last 3 and 5 games they have played. When a player misses a game they get a rating of 0 for the round. I want 0 ratings to be ignored in the running averages. Additionally, games yet to be played have a 0 in the cell and should be ignored. To get overall averages ignoring 0's was simple I used the averageif function, but I couldn't figure out how to use that but only extract the last 3 non 0 values. I currently have data up to round 12, so if a player has played in 12, 11 and 10 then the 3 round average should just be the average of these 3, but if they missed for example round 11 then the 3 round average should return the average of 9, 10 and 12.

The data is set out in the following way.

In column B I have a list of player names from cells B3:B618.

Across row 1 from N1:AM1 are labels "round 1" "round 2" etc.

The ratings for each player in each round are in N-AM and from 3-618. i.e. N3:AM3 contains the weekly rating for player 1, 4 contains player 2 etc.

I would like the 3 round averages to be returned for each player in column L and 5 round averages in column M.

I have the formula for calculating a 13 week rolling average, but I need to be able to set goals in the future and still have the average calculate from current and not from future.

I have my dates set up in row 3 (these dates are all mondays, not every day) and my data in row 4. I need the 13 week rolling average to calculate starting from the prior monday and going back 12 more mondays for the 13 weeks. IE: This monday I'm running reports that the info will be entered into the previous week's monday.

Here is the formula that I'm currently using: =AVERAGE(OFFSET(K4,,COUNT(K4:BV4)-13,,13)) The formula works but will calculate the average for 13 weeks prior based on the last cell with data in it. It's calculating the average of the last 13 weeks of next year.

how to make this work without having to remember my weekly goals every week??

I am trying to create a rolling 30 day average based on a date in a particular cell. I have 62 columns that I am looking at. We can call A1 7/1/14 and the 62nd column 8/31/14 for simplicity sake. I want to grab 30 values in row 2 as these columns are filled in daily so 8/1/14 takes the average of 7/2/14-8/31. On 8/5, I want 7/6-8/4 so on and so forth. The average that I need has to include at least one averageif stating that only nonzero "<>0" figures should be tabulated (just have business day transaction data but need all dates listed to tabulate 30 days). The range of dates will be fixed every month and info copied to tabulate on the first day.

In VBA, is it possible to write to a text file, on 1 single line (without carriage return or line feed) the values of a dynamic array? For instance, I have the array:

Dim my(n) As Double 'n is changeable my(0) = 0 my(1) = 1 my(2) = 2 .....

I want to write to a text file, like this: Write #filename, my(0), my(1), my(2) '..... until my(n) but n is changing

I want them on 1 line, because I use the other direction (up - down) for other things).

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

I am trying to do is create a total of only the last 90 days of entries from a list of $ amounts. Attached is an example. We are going to start keeping track of our cashiers variances but at the top of the sheet we only want to know what their last 90 days of entries equal.

This would be considered a rolling 90 days, so older variances drop off after 90 days, they would still be left on screen but would not effect the main total.

I have a column of values (E4:E87). I'm adding 1 value to this column manually each day...so tomorrow (July 24th) I will have E4:E88...and so forth. Each day is a new row.

In column Z (i.e. cell Z87) I have the formula =(SUM(O53:O87))/(SUM(E53:E87)). Z88 will have =(SUM(somevalue:O88))/(SUM(somevalue:E88)). This gives me a resulting percentage for a rolling period of time.

I need an automated way to populate "O53" and "E53" ("somevalue") in the formula so that the SUM of the current row's cell (i.e. E88) through [whatever] is 50 or greater. (The sum of the values should be as small as possible, but at least 50, and should not include any older values than absolutely necessary for it to equal at least 50.)

An image of my spreadsheet can be viewed here: [URL]

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.

I basicaly need to look down column Q8 to Q52 for the falue "C19" If that value is present - Then I need to look up the value of associated with what is in its respective cell in column O8 to O52. Then add them all together.

I have the formula that follows and it works, however it is huge. I have to repeat the IF,Vlookup 44 times. Just wondering if there is a better way.

Here is the formula I am using.

=SUM((IF(Q8="C19",(VLOOKUP(O8,clusterequipmentvalues,2,FALSE)),0)), (IF(Q9="C19",(VLOOKUP(O9,clusterequipmentvalues,2,FALSE)),0)), (IF(Q10="C19",(VLOOKUP(O10,clusterequipmentvalues,2,FALSE)),0)), (IF(Q11="C19",(VLOOKUP(O11,clusterequipmentvalues,2,FALSE)),0)), (IF(Q12="C19",(VLOOKUP(O12,clusterequipmentvalues,2,FALSE)),0)), .... all the way to C52.

need to write this forwula for excel 1000((value from cell)+.256)=(((x+.314)1000)+9)1.0625. I would like to plug in a value from a cell and find the comparative value x. I am comparing the airfregiht cost between two carriers that have different surcharges for fuel, secuirty, and tax. One has no security or tax.

I'm using a code to copy text from a word doc into a spreadsheet. It does it all right, but when it comes to saving, it does not save the open worksheet and prompts me to save as a copy. If I do so, nothing is changed in the original file and the copy file has the information copied from the word file.

This is the code I'm using to write and save the spreadsheet.

I am trying to write a formula using IF, OR & AND combined together, but I can't see to get my formula in the correct order. I have tried moving several sections around but I still keep getting an error.