Average duration for a specific type and year
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
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try something like this...
=AVG(COLLECT([Duration (days)]:[Duration (days)], [Update Type]:[Update Type], "ANNUAL UPDATE", [Live Date]:[Live Date], YEAR(@cell) = 2019))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sorry for the delay in responding - that worked! Thank you very much for the help!
Greatly appreciated!
Peggy
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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
-
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)), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - thank you very much! that works. I need to read up more on @cell.
Have a great day!
Peggy
-
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".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome is there a way to do something similiar to this in looking at just the last 90 days?
-
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!