AVG Collect Formula Help

I am looking to add to the formula so that if {APD CI Tracker Range 5}, ="Pilot/ Implemented" the Dashboard reads "Pilot" OR if {APD CI Tracker Range 5}, ="Monitor/ Check" the dashboard reads "Monitor" and if {APD CI Tracker Range 5} is not Working, Pilot/ Implemented, or Monitor/ Check the Dashboard reads 0

Current Formula: =AVG(COLLECT({APD CI Tracker Range 9}, {APD CI Tracker Range 11}, ="Problem Solving Group 4", {APD CI Tracker Range 5}, ="Working"))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Set the Sheet Summary field as text\/number then add +\"//www.santa-greenland.com/community/discussion/comment/\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p>

=MAX([Modified]:[Modified]) + \"//www.santa-greenland.com/community/discussion/comment/\"<\/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":107030,"type":"question","name":"How to subtract percentages?","excerpt":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…","snippet":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will…","categoryID":322,"dateInserted":"2023-06-28T15:56:57+00:00","dateUpdated":"2023-06-28T16:22:44+00:00","dateLastComment":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":150369,"lastUserID":150369,"lastUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3375939165,"url":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107030,"commentID":382885,"name":"Re: How to subtract percentages?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382885#Comment_382885","dateInserted":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T16:35:51+00:00","dateAnswered":"2023-06-28T16:31:26+00:00","acceptedAnswers":[{"commentID":382883,"body":"

@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/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":107008,"type":"question","name":"COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","excerpt":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in, I would like to create in another sheet a count by month of that \"created date\" column, to see how many leads came in in January, in February,…","snippet":"Hello Everyone! First time asking for help, I always found my answers within everyone else's questions, but not this time! We work off one main sheet where all our leads come in,…","categoryID":322,"dateInserted":"2023-06-28T12:43:18+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:36:21+00:00","insertUserID":121475,"insertUser":{"userID":121475,"name":"Dan Benitah","url":"https:\/\/community.smartsheet.com\/profile\/Dan%20Benitah","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTteTBX82EPc5KIaiSjoPiuvvmUQ_FSNi2hlGCTngDw=s96-c","dateLastActive":"2023-06-28T18:25:32+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-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":41,"score":null,"hot":3375931179,"url":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107008\/count-cells-by-month-within-one-range-countifs","format":"Rich","lastPost":{"discussionID":107008,"commentID":382886,"name":"Re: COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382886#Comment_382886","dateInserted":"2023-06-28T16:36:21+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-06-28T18:51:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/7UUHRXPOF0WT\/screen-shot-2023-06-28-at-8-35-55-am.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2023-06-28 at 8.35.55 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T18:29:37+00:00","dateAnswered":"2023-06-28T14:08:52+00:00","acceptedAnswers":[{"commentID":382837,"body":"

The formula from @Paul Newcome<\/a> should work, though the below would be an alternative:<\/p>

=COUNTIFS({Date created Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row)))<\/p>

Either of these should work.<\/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