Average Days

philsalamon
philsalamon ✭
edited 12/09/19 inFormulas and Functions

I am looking to have some metrics set up on a dashboard all around average days to close. I have a column for a sales person, two date columns (one a start date and one a close date) and a column called Workflow Status. How can I do the two following things via formulas:

  1. Filter this first by the workflow status being equal to one specific status
  2. Show the average close date by year?
  3. Show the average close date by sales person?
  4. Maybe even break it down by both year and sales person?

I was thinking of using the Netdays but I am unsure how I can filter that by the critera I am looking at.

Thanks for the help.

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@Kris Peeters<\/a> <\/p>

you should be able to use =Countifs([Al Javor]@row:[Lisa Young]@row,\"1 - High\")<\/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":108307,"type":"question","name":"Formula to Average Performance Score for Various Service Categories","excerpt":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…","snippet":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\"…","categoryID":322,"dateInserted":"2023-07-31T15:35:13+00:00","dateUpdated":"2023-07-31T15:48:17+00:00","dateLastComment":"2023-07-31T18:57:50+00:00","insertUserID":164346,"insertUser":{"userID":164346,"name":"ullkay95","url":"https:\/\/community.smartsheet.com\/profile\/ullkay95","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bmtAmxLQVL0!e6DCx07vJ9c!25n9oP55COS","dateLastActive":"2023-07-31T18:59:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164346,"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-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":35,"score":null,"hot":3381654183,"url":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","format":"Rich","lastPost":{"discussionID":108307,"commentID":388084,"name":"Re: Formula to Average Performance Score for Various Service Categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388084#Comment_388084","dateInserted":"2023-07-31T18:57:50+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-31T19:57:04+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\/2HQ91LNQF3GG\/screenshot-2023-07-31-114610.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-31 114610.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-31T18:52:12+00:00","dateAnswered":"2023-07-31T18:48:05+00:00","acceptedAnswers":[{"commentID":388078,"body":"

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name. <\/p>

[Column name]:[Column name]<\/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":108204,"type":"question","name":"I am trying to return where a contract is in the review process, DOJ review may or may not have date","excerpt":"=IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(OR(DOJ@row > [To Contractor]@row, \"DOJ\", IF(DOJ@row = 0, IF([To Contractor]@row > [To OC&P]@row, \"Out for Signature\"), IF([To Contractor]@row > [HSD Signed]@row, \"Out for Signature\", \"//www.santa-greenland.com/community/discussion/54566/\"))))))","snippet":"=IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(OR(DOJ@row > [To Contractor]@row, \"DOJ\", IF(DOJ@row = 0, IF([To Contractor]@row > [To…","categoryID":322,"dateInserted":"2023-07-27T17:35:54+00:00","dateUpdated":"2023-07-27T17:36:30+00:00","dateLastComment":"2023-08-01T00:09:58+00:00","insertUserID":164200,"insertUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":164200,"lastUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":52,"score":null,"hot":3381330352,"url":"https:\/\/community.smartsheet.com\/discussion\/108204\/i-am-trying-to-return-where-a-contract-is-in-the-review-process-doj-review-may-or-may-not-have-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108204\/i-am-trying-to-return-where-a-contract-is-in-the-review-process-doj-review-may-or-may-not-have-date","format":"Rich","lastPost":{"discussionID":108204,"commentID":388134,"name":"Re: I am trying to return where a contract is in the review process, DOJ review may or may not have date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388134#Comment_388134","dateInserted":"2023-08-01T00:09:58+00:00","insertUserID":164200,"insertUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+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-08-01T00:10:04+00:00","dateAnswered":"2023-08-01T00:09:58+00:00","acceptedAnswers":[{"commentID":388134,"body":"

This took me a few hours to finally figure out and clearly, I have much to learn related to SmartSheet function rules. This is the final formula that worked. Sharing in case others may have a similar need. <\/p>

=IF(AND(ISBLANK(DOJ@row), ISDATE([To Contractor]@row)), \"Out for Signature\", IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(DOJ@row > [To Contractor]@row, \"DOJ\", IF([To Contractor]@row > [HSD Signed]@row, \"Out for Signature\", IF(DOJ@row <> [To Contractor]@row, \"Out for Signature\"))))))<\/p>

Once tested, this can be converted to column formula.<\/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":[]}">

Trending in Formulas and Functions