I am attempting to average a column where the assignments are now closed (or past the due date) for the year. The Year@row is 2021.
=AVG(COLLECT({Row Averages}, IFERROR(YEAR(@cell), 0) = Year@row, {QAlerts Closed}, 1)))
Are you getting an error or an unexpected result?
Is "2021" manually entered into the Year column or is it populated by a formula? If it is a formula, what is the exact formula (copy/paste from sheet is preferred)?
What type of column is the {QAlerts Closed} range? How is that populated?
What type of column is the {Row Averages} range, and how is that one populated as well?
ok lets see if I can tackle these.
2021 - is entered the first column of the worksheet so I don't have to update formula years every year. Does that make sense?
QAlerts range is from another sheet.
Row Averages looks like this: (avg the row for closed on time - which is a checkbox)
I count the number of checked columns (formula to check the column if it is completed by due date).
While reading your responses and comparing to your original post, I noticed a missing component from your formula. Let's try getting this part squared away before we dive further than we need to into anything else...
You are missing a range for the YEAR criteria.
=AVG(COLLECT({Row Averages}, {You need a range here}, IFERROR(YEAR(@cell), 0) = Year@row, {QAlerts Closed}, 1)))
So the range <1/1/21 and <12/31/21???
No. That would be the date column that contains the date where you are searching for the year. Kind of like how you have
{QAlerts Closed}, 1
to search the range for a 1
You need a range to search for a year.
Still getting an error message.
The range should look the same as the other ranges. See how you only have the other two ranges listed once?
I confuse easily so I am not sure I am totally getting this or explaining it well.
Quality Alert sheet has the initial dates.
The worksheet where I am gathering the information has the YEAR so I can track from year to year.
Right. So you should have a cross sheet reference that links to whatever date column in the source sheet you are basing the year off of.
=AVG(COLLECT({Row Averages}, {QA Initial Date}, IFERROR(YEAR(@cell), 0) = Year@row, {QAlerts Closed}, 1)))
Paul - you're the best! Thank you!!!
Happy to help. 👍️
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.
