Average that meets Criteria 1 or Criteria 2

kss5229
kss5229 ✭✭
in Formulas and Functions

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?

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    edited 10/14/24 Answer ✓

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

    Screenshot from 2024-10-14 15-04-30.png
    Screenshot from 2024-10-14 15-04-35.png

    Hope this fixes it!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter

Answers

  • SSFeatures
    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!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter
  • kss5229
    kss5229 ✭✭
    edited 10/09/24

    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.

    · Share on FacebookShare on Twitter
  • SSFeatures
    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.

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter
  • SSFeatures
    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?

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter
  • kss5229
    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

    · Share on FacebookShare on Twitter
  • SSFeatures
    SSFeatures ✭✭✭✭✭
    edited 10/14/24 Answer ✓

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

    Screenshot from 2024-10-14 15-04-30.png
    Screenshot from 2024-10-14 15-04-35.png

    Hope this fixes it!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter
  • kss5229
    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
        )
)

    · Share on FacebookShare on Twitter
  • SSFeatures
    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!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!