Sorry this post has gone without reply and welcome to the forums. It greatly helps the community of users to assist helping you when you come to the forums for assistance if you also share a packaged workbook with sample data. Please read through this post - Packaged workbooks: when, why, how for more information.
agree with Patrick - posting your TWBX workbook will get you a specific answer faster - but in concept recreate your formula above except check for dates less than today() and greater than dateadd with today and -7
to cover the 7 day period then divide the total by 7
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.