Week Number Troubles
Good Morning/Afternoon All,
I have a client that we are counting the number of requests in a ticketing solution. Currently we are using this formula to count all the requests he gathers each week.
=COUNTIFS([Request ID]:[Request ID], NOT(ISBLANK(@cell)), [Created Date]:[Created Date], WEEKNUMBER(@cell) = 7)
Once we gather this counted information, I then was taking the sum of these counts, and dividing them to get the average of requests per week right?
Well here is where I am encountering a problem. Can someone help me figure out the formula to get the average of the tickets received PER WEEK. Also, my "Week Number" function in the first formula seems to be pulling TODAY() as the 8th week in the year.. not the 7th? Can someone help me figure this out.
Here is the formula I was using to try and calculate the average per week:
=SUMIF({Commercial Operations Master Sheet Range 4}, WEEKNUMBER(TODAY())) / 5
Thanks for any help!
Best Answer
-
Isn't the average/week just = total/number of weeks in the dataset? Or are you trying to do the average/day for individual weeks?
To get the number of weeks being analyzed, you can do something like:
=rounddown((today() - min([created date]:[created date]))/7,0)
divide the total number of submissions by this and you'll have your average submissions/week
Answers
-
Isn't the average/week just = total/number of weeks in the dataset? Or are you trying to do the average/day for individual weeks?
To get the number of weeks being analyzed, you can do something like:
=rounddown((today() - min([created date]:[created date]))/7,0)
divide the total number of submissions by this and you'll have your average submissions/week
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!