Average days open

=AVERAGEIF([Closing Date]:[Closing Date], "", [Days ago]:[Days ago])

Im trying to average the amount of days a case is open by selecting the rows that have a blank closing day which means that they still open. but i keep getting #INVALID DATA TYPE

thank you in advance

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Luis Orosco

    I would suggest adding in a second criteria where a different column is not blank so that this isn't including the 10 blank rows at the bottom of the sheet.

    Try something like this:

    =AVG(COLLECT([Days ago]:[Days ago], [Closing Date]:[Closing Date], "", [Primary Column]:[Primary Column], <> ""))

    Then, what column type are you putting the formula into? Make sure it's a Text/Number column.

    Let me know if this works!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!