Changing RYG Balls to Numbers to Create Average
I would like to be able to mark each of these columns using the balls: red (0 value), yellow (50), or green (100), but I want the final column to actually show an average number of the previous columns, for example 65%. Does anyone know a formula that would do that?
Answers
-
L_123 ✭✭✭✭✭✭
=(countif(A@row:G@row,"Yes")+countif(A@row:G@row,"Hold")*.5)/count(A@row:G@row)
Give that a try
Help Article Resources
Categories
I hope this solves the problem. The problem is so granular, you'll laugh. Look at the quotation marks around Not Started and see how they are different than what's around Red and Green. <\/p>
The quotation marks around Not Started are curly and need to be straight like with Red and Green. Maybe you were tinkering with the formula in a text editor or word docs. Anyway, I hope this solves the problem. Good Luck and please let me know, if this solved it. <\/p>
=IF(AND([Status]@row = \"Not Started\", [Start]@row <= TODAY()), \"Red\", \"Green\")<\/p>
thanks<\/p>
Michael<\/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":111670,"type":"question","name":"Check Box Selected \/ Not Selected = YES or No","excerpt":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a check box is not selected it would return an answer of \"no.\" I know I can do a drop down, but there are some other factors that require me to…","snippet":"High Smartsheets Community, Question. Is there a formula for if a check box in a column is selected that it would return an answer of \"yes\" in another column and vice versa, if a…","categoryID":322,"dateInserted":"2023-10-13T21:29:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":147643,"lastUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3394477281,"url":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111670\/check-box-selected-not-selected-yes-or-no","format":"Rich","lastPost":{"discussionID":111670,"commentID":400020,"name":"Re: Check Box Selected \/ Not Selected = YES or No","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400020#Comment_400020","dateInserted":"2023-10-14T00:32:21+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-14T00:27:43+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-10-14T00:31:43+00:00","dateAnswered":"2023-10-13T21:43:32+00:00","acceptedAnswers":[{"commentID":400009,"body":"
Hi @Anthony DAmbrosio<\/a> <\/p> Checkboxes use a 1 for True (yes) and a 0 for False (no). <\/p>