我怎么能通用t (on a separate metric sheet) the average SLA time within a given month?
I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping.
I have a sheet that has an acknowledgement SLA column and a "date of request" (auto column for when the submission was created), and I'm looking to record the average for each month on a separate metric sheet.
I have tried this formula, but it won't work:
=AVERAGEIF({date of request}, MONTH({date of request}) = 2, {Ack SLA})
Best Answer
-
Carson Penticuff ✭✭✭✭✭If I understand correctly, this may help.
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})
Answers
-
Carson Penticuff ✭✭✭✭✭
If I understand correctly, this may help.
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})
-
laney_white ✭✭
YES! Thank you so much!
-
Carson Penticuff ✭✭✭✭✭
I am glad I could help!
Help Article Resources
Categories
The issue is with your arguments overall. Using the Score of 40 (as in your examples)...<\/p>
<\/p>
The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.<\/p>
<\/p>
Try reversing it so that the start is less than the score and the end is greater than the score.<\/p>
<\/p>
Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.<\/p>"},{"commentID":387563,"body":"
You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108172,"type":"question","name":"Auto Fill a row from another row","excerpt":"I am trying to autofill a row with a specific number when a certain drop down in used in another column but I am getting an error. I am not seeing an Automation or a conditional format that supports this so I am assuming that this will require a function but I am not able to figure out which one. IE- When FY23 is chosen…","snippet":"I am trying to autofill a row with a specific number when a certain drop down in used in another column but I am getting an error. I am not seeing an Automation or a conditional…","categoryID":322,"dateInserted":"2023-07-27T11:24:15+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T13:32:05+00:00","insertUserID":153244,"insertUser":{"userID":153244,"name":"Minni","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Minni","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/73SE548DKUM7\/nU7DY4I5SMNR3.jpg","dateLastActive":"2023-07-27T13:51:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163439,"lastUser":{"userID":163439,"name":"Ramzi","title":"Smartsheet Solution Architect","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HwjQ38yvgVY!xejdcS75MaY!5PoO6DBMoFm","dateLastActive":"2023-07-27T15:22:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":44,"score":null,"hot":3380923580,"url":"https:\/\/community.smartsheet.com\/discussion\/108172\/auto-fill-a-row-from-another-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108172\/auto-fill-a-row-from-another-row","format":"Rich","lastPost":{"discussionID":108172,"commentID":387510,"name":"Re: Auto Fill a row from another row","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387510#Comment_387510","dateInserted":"2023-07-27T13:32:05+00:00","insertUserID":163439,"insertUser":{"userID":163439,"name":"Ramzi","title":"Smartsheet Solution Architect","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HwjQ38yvgVY!xejdcS75MaY!5PoO6DBMoFm","dateLastActive":"2023-07-27T15:22:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I1WA1YGXKVSR\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T12:00:22+00:00","dateAnswered":"2023-07-27T11:46:01+00:00","acceptedAnswers":[{"commentID":387480,"body":"
Try this - <\/p>
In your Account Number (CPR) field, use this formula:<\/p>
=IF([Project Name]@row = \"FY23\", \"123-456-789\", IF([Project Name]@row = \"FY24\", \"987-654-321\",\"\"))<\/p>
If you have a lot of values, you may want to consider a reference sheet and then INDEX\/MATCH into that sheet.<\/p>
I hope this helps you. Have a great day!<\/p>
Ramzi<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108147,"type":"question","name":"Impact Urgency Matrix","excerpt":"Any chance anyone has built an Impact Urgency matrix in Smartsheet? We have a project prioritization step in our project management process flow and we are looking to prioritize the Impact and urgency based on a matrix of High, Medium, Low. The if(and formula looks to be rather large. I have an intake sheet were we…","snippet":"Any chance anyone has built an Impact Urgency matrix in Smartsheet? We have a project prioritization step in our project management process flow and we are looking to prioritize…","categoryID":322,"dateInserted":"2023-07-26T21:08:19+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T14:30:07+00:00","insertUserID":160343,"insertUser":{"userID":160343,"name":"Bosakie","url":"https:\/\/community.smartsheet.com\/profile\/Bosakie","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uvto9wOvnzM!hhllYVPw9zs!bCWhLIJ-qyd","dateLastActive":"2023-07-27T12:02:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T15:30:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3380875706,"url":"https:\/\/community.smartsheet.com\/discussion\/108147\/impact-urgency-matrix","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108147\/impact-urgency-matrix","format":"Rich","lastPost":{"discussionID":108147,"commentID":387539,"name":"Re: Impact Urgency Matrix","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387539#Comment_387539","dateInserted":"2023-07-27T14:30:07+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T15:30:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T12:05:18+00:00","dateAnswered":"2023-07-27T00:49:35+00:00","acceptedAnswers":[{"commentID":387427,"body":"
I would suggest a table with three columns. One will house the options for Impact. Another will be the Urgencies, and the third will be the desired outputs.<\/p>
<\/p>
Going down the rows, fill in every possible variation (it shouldn't be too many if there are only three options for each), and then you can use an INDEX\/COLLECT to grab the output.<\/p>
=IFERROR(INDEX(COLLECT({Reference Table Output Column}, {Reference Table Impact Column}, @cell = Impact@row, {Reference Table Urgency Column}, @cell = Urgency@row), 1), \"\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
