AVG Collect adding Month and Year
Hello All,
I am looking to average a Likert scale question of 2 hospitals together, from my list of 9 hospitals. How do I add the month and year so I only average the score for January 2024
=AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B"))
I have done this in the past, AND(IFERROR(MONTH(@cell, 0) = 1, IFERROR(YEAR(@cell), 0) = 2024)
but it isn't working. This is the first time I have done and =AVG(COLLECT maybe that part is not correct.
Any advice would be appreciated,
Lori
Answers
-
So have you tried it without the AND statement?
=AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B", {DATE COLUMN}, IFERROR(MONTH(@cell), 0 ) = 1))
The DATA COLUMN being whatever you have named your date column.
-
I'll try that.
Thank you,
Lori
-
It did not work.
Here is the data and my my data collection sheet I want to average Question 1 for 2 hospitals out of the 9, I collect data on for January 2024, then again in February and so on. I have tried several things without success any help would be appreciated.
-
When I tried the formula below it was #unparseable
=AVG(COLLECT({Question 1}, {Hospital}, "Hospital A", {Hospital}, "Hospital B", {Date Column}, IFERROR(MONTH(@cell), 0 ) =1, IFERROR(YEAR(@cell), 0) = 2024)
-
=AVG(COLLECT({Question 1}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024), {Hospital 2}, "Hospital A"))
I got the above formula to work for 1 hospital but not combing the data for 2 hospitals.
How do I combine the data from hospital A and B?
Lori
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!