Average that meets Criteria 1 or Criteria 2

kss5229

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?

SSFeatures

@kss5229

Ok try this formula:

=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)
		)
	),
	COLLECT(
		{Age},
                {Status},
		@cell <> "Complete", 
		{Date of Notification},
                @cell <= [End Date]@row
	)
)

One COLLECT checks if the closure date is within the start and end dates. The other COLLECT checks the status and the date of notification, and then these results are averaged together.

Hope this fixes it!

SSFeatures

Hi @kss5229,

I formatted your second formula to make it a little easier to read:

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

I think you can make this formula work for your use case, the problem is that COLLECT requires you to specify the range before you specify the criterion. Your formula has 2 criterions, but is missing the ranges for those criterions.

=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)
		), 
                ADD A RANGE HERE
		AND({Status} <> "Complete", 
                ADD A RANGE HERE
		{Date of Notification} <= [End Date]@row)
	)
)

The correct formula will look something like this:

=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)
		), 
                {Status},
		@cell <> "Complete", 
		{Date of Notification},
                @cell <= [End Date]@row)
	)
)

Hope this helps!

kss5229

I seem to be having issues with both options you suggest:

Option 1: I'm getting an Invalid Operation" error

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

		), 
ADD A RANGE HERE

		AND({Status} <> "Complete", 
ADD A RANGE HERE

		{Date of Notification} <= [End Date]@row)

	)

)

Where you said to "ADD A RANGE HERE", would the first range be the {Status} and the 2nd would be {Date of Notification}? The way you have it written out, the 2nd range is within the "AND" function… I'm not sure if that makes sense? Is there supposed to be a comma after each added range? I've tried variations of that, but haven't been able to identify where the issue is.

Option 2: I'm getting a divide by zero error.

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

		), 

                {Status},

		@cell <> "Complete", 

		{Date of Notification},

                @cell <= [End Date]@row

	)

)

I think the example you posted above had an extra ending parenthesis. What I posted above it what I have.

SSFeatures

Oops! I just noticed that my second formula does have an extra parenthesis, thank you for fixing that!

Ok a division by zero error will happen if the COLLECT function does not find any rows. So one of the conditions in the COLLECT statement is causing it to not find any rows.

Let me create a test sheet and try out the formula myself to see if I can figure out what's happening.

SSFeatures

You mentioned:

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.

If your event is not "Complete" then is the "End Date" column empty until after the event is complete? Would it be fine if the formula just checked if the "End Date" is within this month, or if the End Date is empty?

kss5229

https://community.smartsheet.com/discussion/comment/457872#Comment_457872

The conditions I want it to look for are:

1. Event = Not complete
2. Event Notification date < = end date of the month
   1. I have a column with the beginning date of each month and the end date of each month that I use to calculate monthly metrics

SSFeatures

@kss5229

Ok try this formula:

=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)
		)
	),
	COLLECT(
		{Age},
                {Status},
		@cell <> "Complete", 
		{Date of Notification},
                @cell <= [End Date]@row
	)
)

One COLLECT checks if the closure date is within the start and end dates. The other COLLECT checks the status and the date of notification, and then these results are averaged together.

Hope this fixes it!

kss5229

Brilliant, thanks! In hindsight, using 2 collect functions seems so obvious, but for the life of me, I couldn't think of a solution.

I also realized my formula was unnecessarily complicated with the start/end dates since I was only checking month & year. Here's the final formula:

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

SSFeatures

You're welcome! Haha I wasn't sure either, and then I had to go look at the AVG documentation to see if it was actually possible to make it work with 2 collect functions.

Also thanks for posting your final formula, I'm sure it'll help someone in the future when they happen upon this thread!