# Average Formula For Rolling Range?

Jan 24, 2014
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

View 1 Replies
ADVERTISEMENT
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
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

View 2 Replies
View Related
Jul 25, 2014

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]

View 3 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
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
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
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
Feb 13, 2014

Now I'm trying to do 12-month rolling average for my company data?

View 1 Replies
View Related
Oct 7, 2008

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.

View 14 Replies
View Related
Dec 7, 2006

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:

={AVERAGE(IF(B1,B5,B9,B13,B170,B1,B5,B9,B13,B17))}

However, Excel will not accept this formula (I assume an array must be contiguous? Please let me know if that's correct).

View 9 Replies
View Related
Jun 6, 2014

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.

View 5 Replies
View Related
Feb 19, 2012

I am wanting to calculate a rolling monthly average and a rolling weekly average.

The following cells have the headers k2 has Allan, Cell L2 has Bill, Cell M2 has Charlie, Cell N2 has Don, cell o2 has Ellen and Cell P2 has Flora

Column J3 to J14 respectivley has Jan to Dec

The balance of the cells will have the data.

I then need to plot the rolling averages for each person on a gaph as teh months data is filled.

Below is the table:

Monthly Totals 2012AllanBillCharlieDonEllenFloraJan0.0000.0000.0000.0000.0000.000

Feb0.0000.0000.0000.0000.0000.000Mar0.0000.0000.0000.0000.0000.000

Apr0.0000.0000.0000.0000.0000.000May0.0000.0000.0000.0000.0000.000

Jun0.0000.0000.0000.0000.0000.000Jul0.0000.0000.0000.0000.0000.000

Aug0.0000.0000.0000.0000.0000.000Sep0.0000.0000.0000.0000.0000.000

Oct0.0000.0000.0000.0000.0000.000Nov0.0000.0000.0000.0000.0000.000Dec0.0000.0000.0000.0000.0000.000

View 1 Replies
View Related
Aug 21, 2012

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??

View 6 Replies
View Related
Apr 16, 2014

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.

View 1 Replies
View Related
Nov 13, 2008

There are words in a cell and to its right is a number.

I have a name in P5. I need a formula in Q5 to add all the numbers

next to the same name in Range B4:O46.

Michael

View 9 Replies
View Related
Aug 30, 2006

I have a list of 400 cells all in column A. Is there a way to have XL divide them up so that 100 are in column A, 100 in column B, etc., while still treating them like a single column (e.g. Sort will sort them all together)?

View 8 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
Nov 21, 2011

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

View 3 Replies
View Related
Sep 15, 2014

i'm trying to count dates within a 2 week date range. I don't want to update the formula every day to the actual dates that would make up two weeks so I tried this formula but it keeps giving me an error.

VB:

=COUNTIFS(Tracker!I2:I479, "=>" TODAY()+1,Tracker!I2:I479,"<=" TODAY()+14)

View 2 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
Dec 3, 2008

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.

View 4 Replies
View Related
Jul 30, 2014

I have attached a work sheet where I have part of a formula working.

Although what I am trying to achieve is in the example in column B.

It is possible to fill in the gaps as in column B with the task between the time frames.?

View 2 Replies
View Related
Sep 24, 2006

I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)

Does anybody know what this formula would look like?

Example:

-----------------------

...| A | B | C | D | E | F |

-------------------------

1 |....| * |.........| 7 |...

-------------------------

2 |....................| 2 |...

-------------------------

3 |....| * |.........| 6 |...

-------------------------

4 |....................| 1 |...

-------------------------

5 |....| * |.........| 5 |...

-------------------------

6 |....................| ? |...

-------------------------

? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)

View 4 Replies
View Related
Feb 25, 2010

I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.

For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.

View 4 Replies
View Related
Mar 17, 2014

I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).

I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.

If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.

I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)

Equation I used for this:

=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)

=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)

View 2 Replies
View Related
Dec 23, 2011

Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.

=AVERAGEIF($E$4:$E$34,">0")

=AVERAGEIF(E4:E34,">0")

View 9 Replies
View Related
Jan 7, 2008

I'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

View 9 Replies
View Related
Apr 10, 2013

I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.

I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.

View 1 Replies
View Related
Jun 5, 2008

I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.

I have a formula for determining if a date is less than a month old:

=IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)

How can I translate that into averaging a range?

I'm using Excel 2003

View 10 Replies
View Related