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'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 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]
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
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.
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)?
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.
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 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?
----------------------- ...| 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)
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.
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)
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.