Average duration for a specific type and year

Peggy Parchert

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

Paul Newcome

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

Paul Newcome

Take a look at the bold portion...

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

Paul Newcome

Try something like this...

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

Peggy Parchert

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

Paul Newcome

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

Peggy Parchert

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

Greatly appreciated!

Peggy

Paul Newcome

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

Peggy Parchert

@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

Paul Newcome

Take a look at the bold portion...

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

Peggy Parchert

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

Have a great day!

Peggy

Paul Newcome

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

jcouncil

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

Paul Newcome

@jcouncil Your criteria for the date range would be

@cell>= TODAY(-90)