Problem with average formula that finds average over last 343 days excluding previous 7

Hello, I have a formula to find the average from the previous 343 calendar days excluding the 7 most previous days for each cell. So for example on the most recent day (day 1) I want to find the average from day 9 to day 343. If I had data for every day there would be 335 days of measurements for each calculation. For some reason not all cells exclude the previous 7 days, some exclude less. In the spreadsheet I have a column with the averages from the formula I also have a column with averages I put in manually to show the values I want. I highlighted the values that are different from the averages found by the formula. I would appreciate any help, thanks.

This is the formula
=IF(E3<>"",AVERAGEIFS($E$3:E3,A$3:A3,">=" & A3-343,A$3:A3,"<"&A3-7),"")

Originally Posted by benishiryo

hi there. first off, you don't have to use CTRL + SHIFT + ENTER to do your formula. this is a non-array formula.

and secondly, G15 is averaging E3 to E8 because you wanted it to be after or equals to 16-Oct-2015 11:51 AM and before 16-Sep-2016 11:51 AM. cell A8 fits that criteria too. i am guessing you don't want the time taken into account. so maybe:
=IF(E3<>"",AVERAGEIFS($E$3:E3,A$3:A3,">=" & INT(A3)-343,A$3:A3,"<"&INT(A3)-7),"")

Thanks! That works