Averaging Wait Time if an Inbound Call



I have read through many of the other discussions and have not been able to find a solution to my issue. I am trying to average the wait time of inbound calls, based on a column called Offer Duration, and confirming that the column Queue Name has the word Inbound within the field. Below is the formula I am trying, but it comes back #UNPARSEABLE.

Formating of Offer Duration is 0:03:39

=AVG(COLLECT([Offer Duration]:[Offer Duration], [Queue Name]:[Queue Name], "INBOUND",))


  • CaraBart28
    CaraBart28 ✭✭✭✭

    Do you have a screenshot of your sheet that you can share?

  • Genevieve P.

    Hi @NCH

    The AVG function will be unable to parse your request as the data stored in your "Offer Duration" column is seen as Text and not Numerical.

    In this instance, I would suggest just grabbing the minutes from the Offer Duration column for a general average. For example, you could translate any hours to 60 min, and round any seconds to either 1 or 0 to get the "minutes".

    =VALUE(LEFT([Offer Duration]@row, 1)) * 60 + VALUE(MID([Offer Duration]@row, 3, 2)) + IF(VALUE(RIGHT([Offer Duration]@row, 2)) > 30, 1, 0)

    Then since that Minutes column is numerical, you can then use your previous formula for an average:

    =AVG(COLLECT([Minutes Duration]:[Minutes Duration], [Queue Name]:[Queue Name], "INBOUND"))



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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!