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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!