Okay, let me see if I can describe this. First, I believe this is the final version of the formula that you will use. I'm guessing on some of the ranges, but I think it should look very close to this.

=IFERROR(AVERAGEIFS(Sheet2!$B$2:$B$366,Sheet2!$A$2:$A$366,">="&A2,Sheet2!$A$2:$A$366,"<="&A2+6),"")

The point of an AVERAGEIFS (or any other IFS based function such as SUMIFS) is to allow you to select an entire range of data and perform a function (like averaging or summing), but to only do it on cells that meet certain criteria. In this case, you're trying to average 7 days worth of information. So using the formula above, the data you are trying to sum is found in the range B2:B366 on Sheet2 (or at least it will be as time progresses and you keep entering new information). This range that contains all of the information will not change. However, you only need to average a single week's worth of data, not the entire year. So the formula now requires some criteria to help it figure out which cells out of the entire range of data cells it should average. I'm assuming that every cell in the range A2:A366 on Sheet2 contains a single date, and that the dates are sequential starting from Jan. 1, 2017. So the "Sheet2!$A$2:$A$366" part of the formula tells it that the whole year's worth of dates is found in that range, and that the range will not change. An important point to note here is that the A4 I used in my original formula is a typo, it should have been A2. I'm assuming that you are putting this formula in cell B2 on Sheet1 and that cell A2 in Sheet1 contains the date Jan. 1, 2017 (this is based on the picture you linked to in an earlier post). Now, the first criteria you need to give the formula is that you only want the formula to average data cells from Sheet2 that have a date in Sheet2 that is greater than or equal to the date in A2 in Sheet1. That is what the ">="&A2 portion of the formula is doing, telling it to only average cells from Sheet2 with a date greater than or equal to the date in A2 on Sheet1. This is the starting date of the date range that you want to average. I am assuming that each consecutive cell in column A on Sheet1 will be a date that is one week later than the date just above it. So cell A3 is Jan. 8, 2017, cell A4 is Jan. 15, 2017, and so on. The second criteria is that you only want it to average a single week's worth of data. So now you need to give it an ending date for the date range that you want to average. Another note here, I adjusted the formula above so that it's a little easier to explain, so I changed the "<"&A2+7 to "<="&A2+6. In order to get the correct ending date, I am adding the 6 remaining days in a week to the starting date that is found in cell A2 on Sheet1. So for example, if I add 6 days to Jan. 1, 2017, I get Jan. 7, 2017. The date range from Jan. 1, 2017 to Jan. 7, 2017 consists of a grand total of 7 days, or 1 week's worth of information. So for the second criteria, I want the formula to only average information from Sheet2 that has a date that is less than or equal to the ending date in the correct date range. Now, it will look at the entire range of data, including any blank cells, but will only average a grand total of 7 cells from Sheet2, and will then put that average in a single cell in Sheet1. However, as you drag the formula down, it will keep looking at a different beginning date for each week's average, as the date in Column A on Sheet1 will keep advancing by 1 week. Finally, the IFERROR statement will prevent it from showing an error message if there is no data available in any of the cells on Sheet2 that meet the date requirements. You don't have to include it, but you'll get an error message displayed if you don't.

To try to put this in less computer-based language, this formula will look at all of the available information. It will then select cells for averaging that have a date greater than or equal to the first date used in the week, and less than or equal to the last date in the week. Since both of these date criteria have to be met for a cell to be selected for averaging, it will only ever select no more than 7 cells for averaging.

Take a look at this website, as well. It is a good one for explaining the SUMIFS function, which operates almost exactly like the AVERAGEIFS function. The only difference is that the SUMIFS function will sum numbers together, and the AVERAGEIFS function will average numbers together. Make sure you take a look at this site as it will be able to help you understand how the function works a lot easier than I can just using text here.

Excel SUMIF and SUMIFS Formulas Explained • My Online Training Hub