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

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.