Average Ticket Open Time Excluding On Hold Status

Options
Kpennington
Kpennington ✭✭
edited 09/22/23 in Formulas and Functions

Hi There,

I am trying to report average ticket open time, but we do not want anything with a status of On Hold factored into the calculation.

This is my current formula. Open Time is a helper field that subtracts today from date submitted.

=ROUND(AVG(COLLECT([Open Time]:[Open Time], [Open Time]:[Open Time], @cell <> "")), 1)

How would I add the exclusion for anything with status = on hold?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hey @Kpennington

    You can add another column and criteria into your COLLECT function, like so:

    =ROUND(AVG(COLLECT([Open Time]:[Open Time], [Open Time]:[Open Time], @cell <> "", Status:Status, @cell <> "On Hold")), 1)

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!