Get the next 7days average continously

[lati2008]

I need to get the average of next 7days . I'm using this formula
=AVERAGE(OFFSET(B5,COUNT(B6:B12)-7,0,7,1))

This formula supposed to continously get the average of the next seven days but whenever I add rows formula wreck. I add between row 4 and 5. The reference is in B5.
Please help what I'm doing wrong and what to do so I can get the next seven days average and not to wreck the formula when I add rows.

What I need to do is compare the yesterday EPV to the average of the next 7days.

Snap_2.JPG


--EDIT:

So as you can see when I add a new row, the formula does not include the new data I added

[TMS]

Attach a sample workbook (not a picture or pasted copy). 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.

[63falcondude]

Try AVERAGEIFS.

Something along the lines of:

=AVERAGEIFS(B:B,A:A,">="&TODAY()-7,A:A,"<="&TODAY())

[lati2008]

sorry, but how to make it only next 7days after yesterday. As you can see I'm collecting data based on yesterday report. And so what I need is to compare that EPV yesterday to the average of next seven days (excluding yesterday).

[lati2008]

already attached the spreadsheet.

Thank you.

[José Augusto]

Hi

I suppose this helps you. Try

Please Login or Register  to view this content.

[lati2008]

Hi

I suppose this helps you. Try

Please Login or Register  to view this content.

This works. Thank you.

[José Augusto]

You are welcome.
Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.