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

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Genevieve P.
    Genevieve P. Employee
    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!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Dave Godfrey
    Dave Godfrey ✭✭✭✭

    HI @Genevieve P


    Your example has worked perfectly thank you.


    Kind regards

    Dave

  • Hi Dave,

    I'm glad that worked for you! Hope you have a good weekend.

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!