AVERAGEIF value between a date range
Hello,
I am creating a metrics sheet that cross references an archive sheet and am looking to pull statistics within monthly ranges.
I want to return a monthly average of the Total ADL Score within a monthly range which is determined by the ARD (Due) Date column. This data is returned to my metrics sheet (Sheet name: Avon Archive Case Mix Metrics). For example, I need the Average ADL Score in January, February, March and so on.
This is my formula and I am receiving an #INCORRECT ARGUMENT SET error.
=AVERAGEIF({Archive Avon CMI Weekly Meeting Form Range 1}, >=
DATE(2022, 1, 1), {Archive Avon CMI Weekly Meeting Form Range 1},
<=DATE(2022, 1, 31), {Archive Avon CMI Weekly Meeting Form Range 3})
I am referencing a data range (Range 1) and a number column (Range 3)
Here are some screen shots:
Range 1 is ARD (Due) Date
Range 3 is Total ADL Score
Best Answer
-
Try the below:
=AVERAGEIF({Archive Avon CMI Weekly Meeting Form Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Archive Avon CMI Weekly Meeting Form Range 3})
Answers
-
Try the below:
=AVERAGEIF({Archive Avon CMI Weekly Meeting Form Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Archive Avon CMI Weekly Meeting Form Range 3})
-
It worked, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!