How to Structure Formula for a 4 Week Rolling Average

[EverClever]

Hello All,

I have a row of data with a combination of blank cells and data-filled cells and need to derive an equation to return only a 4 week average from multiple data points and returning blank cells in between just by copying and pasting across the row.

Starting Column = Y (Monday)
Ending Column = HQ

Week Number = Row 9
Date = Row 10
Day = Row 11
Daily Data Totals = Row 200

Thank you,

Patrick

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[EverClever]

John Topley,

I have uploaded the Run Log sample worksheet.

Thanks so much for your help!

Patrick

[Pete_UK]

You can put this formula in cell AL201 (there's no point in putting it in any earlier):

=IF(AND(MOD(AL$9,4)=0,AL$9<>AM$9),SUMIFS($Y$200:$DE$200,$Y$9:$DE$9,">"&AL$9-4,$Y$9:$DE$9,"<="&AL$9)/4,"")

then copy this across to DE201. It will give you the average at the end of every 4 weeks.

Beyond that cell, you should reset the ranges to cover DG to HP, as your week numbers are reset.

Hope this helps.

Pete

[EverClever]

Pete,

Thank you for the formula...it works somewhat:

4 of the results were 0 and there were always values to add up every 4 weeks

Thanks again,

Patrick

[Pete_UK]

You need to change the formula in EG201 to this:

=IF(AND(MOD(EG$9,4)=0,EG$9<>EH$9),SUMIFS($DG$200:$HQ$200,$DG$9:$HQ$9,">"&EG$9-4,$DG$9:$HQ$9,"<="&EG$9)/4,"")

then you can copy this across to column HQ.

Hope this helps.

Pete

[JohnTopley]

At DG201

change formula to ....

=IF(AND(MOD(DG$9,4)=0,DG$9<>DH$9),SUMIFS($DG$200:$ZZ$200,$DG$9:$ZZ$9,">"&DG$9-4,$DG$9:$ZZ$9,"<="&DG$9)/4,"")

[EverClever]

Pete,

The revised formula:

Miscalculated the 1st 4 week average; 38 correct answer / 81 incorrect answer

Skipped the 2nd 4 week average

Miscalculated the 3rd 4 week average; 69 correct answer / 93 incorrect answer

Misplaced (at a 5 week interval) and Miscalculated the 4th 4 week average; 82 correct answer / 65 incorrect answer

Misplaced (at a 5 week interval) and Miscalculated the 5th 4 week average; 86 correct answer / 72 incorrect answer

No results for the 6th, 7th, 8th and 9th 4 week intervals

Pete & John,

Both of your revised formulas range from column EG or DG to KL or LK respectively but my data ranges from Y to HQ so not sure if that is part of the problem?

John,

Appreciate your formula input but only derived 4 of 9 data points.

Patrick

[Pete_UK]

From the formula that you had originally put in AL201, i.e.:

=SUM(Y200:AL200)/4

(which yields 38.0), I concluded that you want to add all the numbers on row 200 which are in the 4-week range, and then divide them by 4. This is what my formulae do, taking the end of the 4-weeks as the week number which is evenly divisible by 4 (i.e. 36, 40, 44 etc.). On row 201 I have manually put in similar formulae (shown in blue) to calculate each 4-week average where I would expect it to appear. The two formulae that I have given you (i.e. either side of the new year) have been entered on row 202 and shown in red on the attached file. They all occur where expected, and the red and blue numbers are the same for any 4-week period.

I can't understand why you think there is a discrepancy.

Hope this helps.

Pete

[EverClever]

Pete,

For some unknown reason, I had to repast the formula again and everything added up correctly and in the proper place. Thank you so much for your help with this. I would have never been able to figure this one out on my own! I do have another formula that will be based on this one but will try to configure that one and then reach out for help if I can't figure it out which will probably be the case.

Patrick

[Pete_UK]

Glad you got it working in the end, Patrick.

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

Also, 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