AVG Collect adding Month and Year

Lori C

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

Dakota Haeffner

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.

Lori C

I'll try that.

Thank you,

Lori

Lori C

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.

Lori C

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)

Lori C

=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