I am trying to calculate the average percentage of a range of values but only for certain dates


I am trying to calculate the average percentage of a range of values but want to include values that are within a specified date range- i.e from 1/1 to 3/31 or before x date or after x date based on information in a specified date column. In this case I have a column for Training Date and a column for Training Completion % and in the parent row I would like to be able to calculate the Average Training Completion % of the child rows but only for the training classes within certain dates or before or after certain dates.

Any suggestions?

Thank you!


  • Katy H
    Katy H ✭✭✭✭✭✭

    @Emily Sanchez are you wanting to average the training percent completion for a variety of date ranges? Or only one?

    The short answer is yes, you can, with an AVG/COLLECT process, but I want to make sure I understand the ask before giving you a more complex answer.

    Katy Hall

    Head of Product Management

    ILLA Canna


  • Emily Sanchez
    edited 03/30/22

    Just for one- I'm trying to get the average training class completion rate for a particular quarter i.e for training classes within Q2 of this year, so if the training date falls within 4/1- 6/30 of 2022, I want to include the completion percentage in the list I'm trying to get the average of...

  • For clarification- here is what I have currently:

    The Training/90 TP % is what I'm working with currently, the parent rows are set to average the children rows and give the average percentage. I would like to only average the percentage for the children where the value in the Training end date column is within a particular date range, in this case 2nd quarter of 2022 so 4/1 through 6/30.

    Does that make sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!