Average Collect - Find Overall Class Rating in a Date Range

Hi Smartsheet Community!

I'm looking to average an overall class rating for classes that took place from January 1, 2023, to January 31, 2023. Below is the calculation I have so far, but I keep getting incorrect argument set.

Any ideas would be greatly appreciated. Thank you :)

=AVG(COLLECT({Class from Feedback Sheet}, HAS(@cell, [Column 2]@row), {Rate Class Overall}, {Date Attended Class}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 1, 31))))


Best Answer

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

    Looks like your syntax is off. The range to average should be the first range followed by your range/criteria sets.

    =AVG(COLLECT({Rate Class Overall}, {Class from Feedback Sheet}, HAS(@cell, [Column 2]@row), {Date Attended Class}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 1, 31))))

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!