AVG(COLLECT)

Elizabeth Aird

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)))

Paul Newcome

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?

Elizabeth Aird

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).

Paul Newcome

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)))

Elizabeth Aird

So the range <1/1/21 and <12/31/21???

Paul Newcome

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.

Elizabeth Aird

Still getting an error message.

Paul Newcome

The range should look the same as the other ranges. See how you only have the other two ranges listed once?

Elizabeth Aird

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.

Paul Newcome

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)))

Elizabeth Aird

Paul - you're the best! Thank you!!!

Paul Newcome

Happy to help. 👍️