Average duration for a specific type and year

Peggy Parchert
Peggy Parchert ✭✭✭✭✭✭
edited 02/24/20 in Formulas and Functions

I'm trying to get a formula to work in the Sheet Summary for a sheet I have that collects data on updates I would like this formula to tell me the Average duration of a specific type of update for a specific year.

The columns my formula is looking at:

Duration (days) - Text/number column

Live Date - date column

Update Type - dropdown list (ANNUAL UPDATE is listed)

Here is what I have so far:

=AVG(COLLECT([Duration (days)]:[Duration (days)], [Update Type]:[Update Type], "ANNUAL UPDATE", [Live Date]:[Live Date], YEAR(2019)))

It is not working - I'm getting an #INVALID DATA TYPE error.

Any suggestions to get this working would be greatly appreciated.

Thanks, Peggy

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!