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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    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))

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Peggy Parchert
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

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

    Greatly appreciated!

    Peggy

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Peggy Parchert
    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

  • Peggy Parchert
    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
    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".

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • jcouncil
    jcouncil ✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @jcouncil Your criteria for the date range would be


    @cell>= TODAY(-90)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!