Average number of day formula

I have these two columns "Date Created" and "Date Closed". I need to write a formula that looks at all 1000+ rows of data and produces an average amount of days it takes to close a row of data. The formula will be in my calculations sheet. So, I'll be referencing this data. Should I use the "averageifs"? I really appreciate the assistance with this formula.


Best Answer

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭
    Answer ✓

    Hi FGCC Smartsheet,

    To round the average you can use ROUND function.

    =ROUND(AVG([No. of Days]1:[No. of Days]30), 0)

    Thank You!

    Anjanesh Vaidya

    Thanks,

    Anjanesh Vaidya

    Smartsheet Development, Ignatiuz Software

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️

Answers

  • I've tried several different formula and they all return unparesable.

    =AVERAGEIF({File a complaint Range 1}, "<="&TODAY(), {complaints Range 2}, ">="&TODAY(), "<>")

    =AVERAGEIF({File a complaint Range 1}, "<="&TODAY(), {complaints Range 2}, ">="&TODAY(), {File a complaint Range 1}, "<>", "")

    =COUNTIFS({File a complaint Range 1}, ">="&TODAY(), {File a complaint Range 1}, "<="&TODAY())

    Thanks!

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭

    Hi FGCC Smartsheet,

    To get the average amount of days from the rows you can try following formula-

    First, we need to create a helping column "No. of Days" where we will use following formula-

    =[Date Closed]@row - [Date Created]@row

    Then, in "Average Calculation" column we will use following formula-

    =AVG([No. of Days]1:[No. of Days]30)

    Here, you can use the Average Calculation formula in your calculation sheet as mentioned and provide the range accordingly as you mentioned 1000+ rows.

    I Hope it will work!

    Thank You!

    Anjanesh Vaidya

    Thanks,

    Anjanesh Vaidya

    Smartsheet Development, Ignatiuz Software

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️

  • Thanks for that. It almost works exactly right. Only issues I'm having are:

    Regards

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭
    Answer ✓

    Hi FGCC Smartsheet,

    To round the average you can use ROUND function.

    =ROUND(AVG([No. of Days]1:[No. of Days]30), 0)

    Thank You!

    Anjanesh Vaidya

    Thanks,

    Anjanesh Vaidya

    Smartsheet Development, Ignatiuz Software

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!