Average Collect with OR

Lea722
Lea722 ✭✭
edited 02/19/24 in Formulas and Functions

Hi Smartsheet Community!

How can I work OR into this Average Collect formula? I need the "C&P Complete" reference to be "Yes" OR the "Does this position require credentialing?" reference to be "No" worked in.

=AVG(COLLECT({Days B/T Submission & Onboard}, {Provider or Non-Provider From Req Sheet}, "Non-Provider", {C&P Complete}, "Yes", OR({Does this position require credentialing?} "No", {Orientation Date}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31))))

Many thanks!

Lindsay

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 02/16/24
    =AVG(COLLECT(
    {Days B/T Submission & Onboard}, 
    {Provider or Non-Provider From Req Sheet}, "Non-Provider", 
    OR(
    {C&P Complete}, "Yes", 
    {Does this position require credentialing?} "No", 
    )
    {Orientation Date}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 31)
    

    Base on my understanding, you wrap the criteria in OR statment, you do not place it in between or two criteria.

    OR ( This logic, That logic )

    ...

  • Lea722
    Lea722 ✭✭

    Thank you @heyjay! I was able to add a helper column to get to the solution as well. I appreciate your help :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!