Average percentage of Assessments in a date range

We do progress assessments on a monthly basis and the assessments are given a score as a percentage.

I can write the formula to give the average score for all of the assessments on the sheet but I am struggling to write the formula that allows me to find the average score in a date period.

The formula I have tried is

=AVERAGEIF({MOS Assessment Total %}, {MOS Assessment Date}, >=$[Start Date]$1, {MOS Assessment Date}, <=$[End Date]$1)

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Dave Godfrey

    When you have multiple criteria to include in your Average formula (such as looking at two different dates), you'll actually want to use an AVG(COLLECT formula instead. The COLLECT function will filter down the initial range to Average based on your multiple criteria.

    For example:

    =AVG(COLLECT({Range to Average}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2, ...) etc

    So in your instance, try something like this:

    =AVG(COLLECT({MOS Assessment Total %}, {MOS Assessment Date}, >=$[Start Date]$1, {MOS Assessment Date}, <=$[End Date]$1))

    Let me know if this works for you!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!