Formula/function for rolling average of last seven days and 28 days previous to that

Hello,

I have been working on getting a formula/function together for a rolling average. I am having some difficulty getting it all squared away. This is what I am looking at doing

In one sheet I have a database with values corresponding to a specific name and date. Therefore there is several data points corresponding to each specific name. On a separate sheet I am attempting to have the name and two corresponding columns to that name, one with the average of their last seven data points. The second column with the average of the PREVIOUS 28 data points up until the first of the last seven data points. Basically I am looking to compare the last seven days to the last month previous to those seven days. Data will be added frequently so basically it would need to be a rolling average. I was able to get the rolling average function/formula for the last seven days figured, out however the tricky part is figuring out how to get a rolling average of previous 28 days to that. This is the formula I used for the rolling average of the last seven data points. Basically a combo of an if then statement and a rolling average formula.

=IF(Database!C2:C1053=A3,AVERAGE(OFFSET(Database!F2:F1053,COUNTA(Database!F2:F1063)-1,0,-7,1)))



Any help or suggestions would be very much appreciated. I am quite sure that I could be totally off on this and unclear on my description of what I am trying to do.

Thanks again!

If you don't attach a sample workbook then we have to guess how your data is laid out, and what is contained within each column. Where do you put the date of interest, or are you using a reference to TODAY()?

It strikes me that you can use an AVERAGEIFS formula, along the lines of:

=AVERAGEIFS(range_to_be_averaged,date_range,">="&date-35,date_range,"< "&date-7,name_range,A3)

Hope this helps.

Pete

Thank you for your help. My apologies for not attaching a sample. I am not sure if what I was doing is even right anymore.

I have attached an example of what I am working with.

Suppose you put the date 30th Sept 2015 in E2 of Sheet1 (as the data in your sample file only goes up to mid September, so you can't base it on TODAY() ), then in E3 you could have this formula:

=AVERAGEIFS('Database Total'!$F:$F,'Database Total'!$D:$D,">="&E$2-35,'Database Total'!$D:$D,"< "&E$2-7,'Database Total'!$C:$C,$A3)

which can be copied down into E4:E5, to give you the average of column F in the database sheet for each name from between 35 days (inclusive) before the date in E2 and 7 days (exclusive) before that date.

Hope this helps.

Pete

Pete,

Thanks for your help. I have made significant progress with your help. The dates I had in there were just examples. I went ahead and updatedthem to current date using the today()function to make it more simple and the data I am going to enter is going to be from the past couple months. Here is my question now,

I want a separate column to readout the average value for the past seven days so I used this formula where &E$2 is todays date and this one is working just fine.


=AVERAGEIFS('Database Total'!$F:$F,'Database Total'!$D:$D,">="&E$2-7,'Database Total'!$C:$C,$A4)

However I am still struggling to get the formula to read right for the previous 28 days prior to the first of the last seven days. I tried this formula and it is not getting an accurate average where &F$2 is todays date minus seven days. If actually average out the numbers between those dates it is not the same number as this formula reads out.

=AVERAGEIFS('Database Total'!$F:$F,'Database Total'!$D:$D,">="&F$2-28,'Database Total'!$C:$C,$A4)

Am I missing something simple? Thanks again for all the help.

Yes, you are saying (in the formula) that you want to include all data where the date is within the last 35 days. This will obviously include dates that are within the last 7 days, and so you need to include another term which will exclude those dates from the calculation. Try this:

=AVERAGEIFS('Database Total'!$F:$F,'Database Total'!$D:$D,">="&F$2-28,'Database Total'!$D:$D,"< "&F$2,'Database Total'!$C:$C,$A4)

(changes shown in red).

Hope this helps.

Pete

Hello,

I am following up on this formula as I have been going back through it and for some reason it does not seem to be accurately computing the average. Initially I thought it was working correctly but when I go back and compute the average manually it does not seem to be calculating the average right for either the rolling seven day or 28 days previous to that. I am at a loss as to what numbers it is averaging. I have repeatedly gone back through the formula and researched the averageifs and it appears to be the exact formula I should use but somewhere along the way it is not averaging correctly. I have attached a sample workbook to visualize the issue I am running into. If you look just pick a random name and compute the rolling seven day average manually you will find that it is not accurate. Any help is very much appreciated thank you.

You can use this formula in G2:

=IFERROR(AVERAGEIFS(Sheet1!$F:$F,Sheet1!$D:$D,">"&D2-7,Sheet1!$D:$D,"<="&D2,Sheet1!$C:$C,C2),"")

and this one in H2:

=IFERROR(AVERAGEIFS(Sheet1!$F:$F,Sheet1!$D:$D,">"&D2-35,Sheet1!$D:$D,"<="&D2-7,Sheet1!$C:$C,C2),"")

and then copy down.

The formula that you were using did not have an upper limit on the date, so it was evaluating the average for all data from 7 days before the date in D2 to the last date in your data.

Hope this helps.

Pete

Thanks for the help!

You're welcome - glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Alright, I have run into another issue. This is a little different issue but at the same time its so similar that I figured I would still ask in this thread. Would you know how to find the standard deviation for the exact same data set we are doing for the averageifs?

Thanks again!

There isn't an STDEVIFS function, but you can make the equivalent using an array* formula. Put this in cell I2:

=IFERROR(STDEV(IF(($D$2:$D$1000>D2-7)*($D$2:$D$1000<=D2)*($C$2:$C$1000=C2),$F$2:$F$1000)),"")

*Note that an array formula needs to be committed using the key combination Ctrl-Shift-Enter (CSE) rather than the usual < Enter >

It is not a good idea to use full-column references in array formulae, as every element of the range will be calculated (i.e. 1 million + cells), so I have restricted the ranges to from row 2 to 1000.

Hope this helps.

Pete

Thanks.. That helps very much. I will be accumulating a lot of data in the database so I already need more than 1000 cells. I will adjust as needed. If I do not want the averageifs and stdev array formula to count zeros what additional criteria would I add to the formulas? I hope and think this will be the last question. I am very appreciative of the help.

I actually had another formula I am looking to calculate which is a z-score... the z-score is calculated by subtracting the average of the previous day from the raw data point of the current day from column F and then dividing it by the standard deviation of the current day. For example it would look something like this (Data from column F for 8/13/2016-moving average as of 8/12/2016)/(Standard deviation as of 8/12/2016).

Thanks