# Average duration for a specific type and year

Options
✭✭✭✭✭✭
edited 02/24/20

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

• ✭✭✭✭✭✭
Options

Let's add in an IFERROR for the date criteria...

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭✭✭✭✭
Options

That comes back with an #INVALID DATA TYPE error message too. Suggestions? This should be possible right? Or is this not something that can be done from the Sheet Summary?

Thanks Peggy

• ✭✭✭✭✭✭
Options

Let's add in an IFERROR for the date criteria...

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

• ✭✭✭✭✭✭
Options

Sorry for the delay in responding - that worked! Thank you very much for the help!

Greatly appreciated!

Peggy

• ✭✭✭✭✭✭
Options

@Peggy P Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

• ✭✭✭✭✭✭
Options

@Paul Newcome - since you were so helpful with this previously. Hoping you can assist with the "new wrinkle".

I've been asked to find the average of two types: ANNUAL UPDATE & VERSION CHANGE by year

The columns my formula is looking at:

Duration (days) - Text/number column

Live Date - date column

Update Type - dropdown list (ANNUAL UPDATE and VERSION CHANGE are listed)

Current formula only looks for one of the two types and is separate fields on my Sheet Summary.

=IFERROR(AVG(COLLECT([Duration (days)]:[Duration (days)], [Update Type]:[Update Type], "VERSION CHANGE", [GATOR Date]:[GATOR Date], IFERROR(YEAR(@cell), 0) = 2017)), "")

=IFERROR(AVG(COLLECT([Duration (days)]:[Duration (days)], [Update Type]:[Update Type], "ANNUAL UPDATE", [GATOR Date]:[GATOR Date], IFERROR(YEAR(@cell), 0) = 2017)), "")

How do I combine these into just one formula? Is that possible? Any assistance would be appreciated.

Thanks -Peggy

• ✭✭✭✭✭✭
Options

@Paul Newcome - thank you very much! that works. I need to read up more on @cell.

Have a great day!

Peggy

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

@cell basically tells the function to look at the range and evaluate each cell individually. You need it for OR and AND statements like this because both of those functions require "logical statements" (this is equal to that). So you use the @cell like this to basically tell the formula to collect based on which cells within the range equal "specific text".

• ✭✭✭✭
Options

@Paul Newcome is there a way to do something similiar to this in looking at just the last 90 days?

• ✭✭✭✭✭✭
Options

@jcouncil Your criteria for the date range would be

@cell>= TODAY(-90)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!