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
-
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!
-
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
-
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⬆️
-
@Anjanesh Vaidya Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!