Adding 1 more condition to AVG () with Collect() formula

Marysa

I have successfully used the below to average the aging for help tickets that were reported between 2 dates. I need to add one more condition, so that it only provides the average if the severity of that ticket is Urgent or High (using @row). I cannot figure out where/how to add this third condition.

=AVG(COLLECT({Closed Severity}, {CLOSED ITEMS Date Created}, =DATE(2024, 5, 6), {CLOSED ITEMS Date Created}, =DATE(2024, 5, 31)), {Closed Aging})

I tried nesting the AND function within the COLLECT as a third condition but this gives me an Invalid Data Type error.

=AVG(COLLECT({Closed Severity}, {CLOSED ITEMS Date Created}, =DATE(2024, 5, 6), {CLOSED ITEMS Date Created}, =DATE(2024, 5, 31), AND(({Closed Severity}), =[Primary Column]@row)), {Closed Aging})

KPH

You’re almost there @Marysa

You do need to add the condition to the COLLECT, so that your average only includes rows that meet that condition. However, you do not need an AND function. The collect will only collect rows where all criteria are met (so the AND is sort of assumed).

Try

=AVG(COLLECT({Closed Severity}, {CLOSED ITEMS Date Created}, =DATE(2024, 5, 6), {CLOSED ITEMS Date Created}, =DATE(2024, 5, 31), {Closed Severity},=[Primary Column]@row), {Closed Aging})

Marysa

Hello @KPH, that makes sense that I do not need AND since it's assumed within the COLLECT function. I tried your recommendation and it cleared the error. However, it's still calculating as though I didn't add that third condition based on severity and it's just giving the average for all items between those 2 dates (I'm able to validate what the answer should be by filtering on my source sheet by the criteria and seeing the average). I then tried moving the third condition up in sequence in case there is some type of hierarchy being applied but same result.

=AVG(COLLECT({Closed Severity}, {Closed Severity}, =[Primary Column]@row, {CLOSED ITEMS Date Created}, =DATE(2024, 5, 6), {CLOSED ITEMS Date Created}, =DATE(2024, 5, 31)), {Closed Aging})

I also then tried changing the dates to be between 5/13/24 and 5/17/24 just as another test. This also produced the same result, which makes me think that I might have something wrong with my conditions for the dates.

KPH

Hi @Marysa

I didn’t look at your dates but they do indeed look like they’d be a problem. You have the requirement that CLOSED ITEMS Date Created is 6 May 24 and also 31 May 24. I don’t think that will be possible. Do you want the average for rows between those dates? If so, change = to >= and < = .

Marysa

Thank you @KPH, that fixed the dates. It still was not calculating correctly but I finally figured it out. The range for the Collect function needed to point to my aging column but I also needed to remove the Closed Aging from the end of my formula because it seemed to have been pointing to the number 2 of my Average function.

=AVG(COLLECT({Closed Aging}, {Closed Severity}, =[Primary Column]@row, {CLOSED ITEMS Date Created}, >=DATE(2024, 5, 13), {CLOSED ITEMS Date Created}, <=DATE(2024, 5, 17)))

KPH

Yes, your average was the average of the things in the cells referenced as closed severity that met the COLLECT criteria and whatever closed aging referenced. The revised version will average the cells referenced as closed aging where the row meets the COLLECT criteria. Hopefully this is what you want. 🤞🏻 If you have any trouble, share a screenshot of your sheet and let us know which columns are which and what you are trying to average.