# Calculating Rolling Average Ignoring 0 Values

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
ADVERTISEMENT
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
Oct 18, 2009

I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.

This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.

View 9 Replies
View Related
Aug 30, 2013

I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.

The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.

View 4 Replies
View Related
Dec 4, 2013

1.jpg

I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)

View 5 Replies
View Related
Jul 28, 2006

I created a pivot table in which sales amounts are represented and a derived table in which market share percentages are showed. Now i am looking for a formula that is able to calculate average market share values. Depending on which country i selected in the pivot table and which category, the number of active companies are changed. How can i formulate this formula to calculate average market share percentages in a certain period. check out my attached file to clarify the situation.

View 2 Replies
View Related
Oct 12, 2007

I want to omit null values from monthly averages I'm calculating for some in consistent data. Currently, the macro I wrote reads the empty cells and I believe is viewing them as zeros. When taking the monthly averages, in some cases on parameter 'X' might be sampled for a particular date at a location, but another parameter 'Y' isn't sampled for whatever reason on that particular date at the location. Therefore, there is no value in the cell for parameter 'Y' for that particular date (the value is null). When the macro runs, it sees the blank cell for the particular missing date, but I believe it still views that as a 0 value which it includes in the average, instead of overlooking that cell b/c it is an unknown. So, for example, it might thinks there are actually six actual sampling results instead of five, and calculates the average based on six being the total instead of five which seems to misrepresent the average. (e.g., it's currently viewing 2, 2, Null, 2, 2, 2 as n=6 instead of n=5)

If possible, I'd like to keep the structure of the code as below with only the minimal modifications to address this issue.

I do have Options-->Window Options-->Zero Values de-selected....

View 9 Replies
View Related
Jun 15, 2014

I have a list of houses for sale in various suburbs, divided into houses with various amounts of rooms.

I want to be able to find the average cost of each size house in each individual suburb.

i.e. City Centre, 3 Bedroom, 250000

City Centre, 2 Bedroom, 200000

City Centre, 1 Bedroom, 150000

What is the best way of doing this? Multiple worksheets?

Obviously the sample worksheet I have supplied is quite small, I will be working with much larger lists once.

Sample book.xlsx

View 7 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
Aug 12, 2014

I am using a simple SUM formula like this, =SUM(D10:E10). I would like for the SUM cell to be left blank until a value is entered into the cells D10. Currently it calculates the SUM as 0.

View 13 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
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
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
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
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
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
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
Jan 11, 2009

If I have a formula which adds the value of each day of the week and divides by 7 to get an average per day, how do i fix it to get an average if their is a holiday so their would only be 6 days without changing the formula as the holidays could change from week to week by province.

=(a+b+c+d+e+f+g)/7 what happens if "e" is a holiday and I need to divide by 6, or in the case of xmas if "d and e" are holidays and I need to divide by 5?

View 7 Replies
View Related
Mar 13, 2012

Cell A1 contains 02/29/2012

Cell B1 contains 12/01/2006

I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.

View 7 Replies
View Related
Feb 14, 2013

I'm trying to return the average, min and max values of a range that will have errors, be blank or have zeros. I have already found an array formula that works getting rid of the errors but the zeros are now a problem.

{=AVERAGE(IF(NOT(ISERROR(D27:G27)),D27:G27))}

I was thinking that an OR might be needed but for the life of me can't figure out where it belongs. I will also be using this formula with both MIN and MAX in the adjacent cells so it would be great if all I have to do is swap those in for AVERAGE. I'm not keen on how array formulas work, just found an example similar to this on the interwebs, and I'm curious why if the array brackets are removed the value returned is 0 and not an error.

Workbook is proprietary so VB is locked and not an option (not sure if it was but wanted to be clear).

View 5 Replies
View Related
Jan 4, 2009

Trying to determine the best way to do this. I understand that the standard AVERAGE function will ignore blanks if given a range; the function I'm using does a search for a particular value to determine if a value is to be included in the averaging: ...

View 9 Replies
View Related
Jun 20, 2014

So in Cell K12 there is a rather large formula (I condensed it for this example).

This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7

So based on the formula in K12 I need Cell J12 to Display the answer.

if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.

The problem comes in when a Cell is N/O (Not Observed)

How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.

I know in a simple formula I can add <>0 to the formula to ignore zeros. How to write it into a bigger formula such as in K12.

For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.

View 14 Replies
View Related
Oct 24, 2011

I am trying to get a rolling 8wk avg of a large group of data. I am trying to take the avg of 8 vlookups:

=AVERAGE(VLOOKUP($A$9,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-7,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-14,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-21,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-28,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-35,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-42,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-49,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE))

However, in some cases, the cell to be looked-up may be blank. Using the formula above, the result of these vlookups is "0". I want to take the average of these vlookups excluding the blanks from the 'Raw Data' sheet.

View 6 Replies
View Related
Dec 5, 2011

I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.

I have tried Search but couldn't find anything that address both blank and zero.

Sheet2

K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%

Excel tables to the web : [URL] .......

With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.

View 9 Replies
View Related
Mar 26, 2014

I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.

Formula:

[Code] ....

View 2 Replies
View Related