How to Average Event Ages Based on Either Closure Date or Status in Smartsheet?

System

This discussion was created from comments split from: How to find average considering multiple criteria?.

kss5229

https://community.smartsheet.com/discussion/comment/319542#Comment_319542

Is there a way to do average if a value meets criteria 1 OR criteria 2?

I have a smartsheet with many events that often span several months. I want to calculate the average age of relevant events for each month.

For example, for the month of March, I want it to calculate the average age of events that are still open in March in addition to the age of all events that were closed within the month of March.

I already have a column that calculates the age of each event.

So far, I have a formula that calculates the Age of events that closed within the month.

=IFERROR(AVG(COLLECT({Age}, {Closure Date}, AND(IFERROR(MONTH(@cell), 0) >= MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) <= YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) <= MONTH([End Date]@row), IFERROR(YEAR(@cell), 0) <= YEAR([End Date]@row)))), "N/A")

I want to add to the formula to have it also average events that are not "complete" (I have another column with status that marks events as complete) and a notification date < = end date of the month. This is my attempt:

=AVG(COLLECT({Age}, {Closure Date}, AND(IFERROR(MONTH(@cell), 0) >= MONTH([Start Date]@row), IFERROR(YEAR(@cell), 0) <= YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) <= MONTH([End Date]@row), IFERROR(YEAR(@cell), 0) <= YEAR([End Date]@row)), AND({Status} <> "Complete", {Date of Notification} <= [End Date]@row)))

But it's returning "Invalid Operation".

My understanding of how the "collect" function works is that it's looking for events that are closed within the time frame AND also have a status of not "complete" (which doesn't exist). Is there an easy way to average values that meet criteria 1 or criteria 2 that I'm missing?

kss5229

Solved here: https://community.smartsheet.com/discussion/comment/458225#Comment_458225

Genevieve P.

Hey @kss5229

Thank you for posting your solution! I'm glad you were able to get this resolved.