Seeking solution for averageifs()
I would like to calculate the estimated hours it will take me to complete a task based on how long it took me to complete previous tasks of a similar nature. I've included a screenshot of my project sheet with the relevant columns.
Originally I was using the formula: AVERAGEIF([Deck Intensity]:[Deck Intensity], [Deck Intensity]@row, [Act. Hours]:[Act. Hours])
The problem is this formula doesn't take into account the [Status] value when deciding which rows/tasks to include in the average. I do not want to include rows/tasks that are "in progress" or "not started" in my calculation because it will skew my estimated hours lower than what it actually should be. It should only include the average for rows where [Status] is marked as "complete"
I've tried using several variations of =sumif()/countif() but they all end in an error code.
Here's my logic written out in case that helps anyone else:
In cell [Est. Hours]1, average the values in the [Act. Hours] column for every row where [Status] is equal to "Complete" AND [Deck Intensity] is equal to the value in [Deck Intensity]1.
Any ideas? Thanks in advance!
Best Answer
-
ker9 ✭✭✭✭✭✭See if this works for you:
=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))
Hope this helps!
Answers
-
ker9 ✭✭✭✭✭✭
See if this works for you:
=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))
Hope this helps!
-
@ker9That worked! Thank you so much!
Help Article Resources
Categories
See if this works for you:<\/p>
=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, =\"Complete\", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))<\/p>
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":[]},{"discussionID":112023,"type":"question","name":"How do I add days to an existing date?","excerpt":"I have read through this community about the same question, however, the solutions haven't seemed to work for me. I'm working on a Debt Snowball sheet to calculate and estimate when my bills will be paid off. I can use EITHER the Months or Days column from the DATE column. For example - need the formula to add 8 months (or…","snippet":"I have read through this community about the same question, however, the solutions haven't seemed to work for me. I'm working on a Debt Snowball sheet to calculate and estimate…","categoryID":322,"dateInserted":"2023-10-23T16:13:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-23T21:27:09+00:00","insertUserID":168908,"insertUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168908,"lastUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3396175262,"url":"https:\/\/community.smartsheet.com\/discussion\/112023\/how-do-i-add-days-to-an-existing-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112023\/how-do-i-add-days-to-an-existing-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112023,"commentID":401250,"name":"Re: How do I add days to an existing date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401250#Comment_401250","dateInserted":"2023-10-23T21:27:09+00:00","insertUserID":168908,"insertUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+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\/VA3KH8XYWXBA\/snowball-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png"},"alt":"Snowball.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-23T23:08:08+00:00","dateAnswered":"2023-10-23T16:22:41+00:00","acceptedAnswers":[{"commentID":401195,"body":"
Hi @Jenna P<\/a>,<\/p> If your DATE and Paid off by (est.) columns are both set to date then in the latter put the following formula:<\/p> =Date@row + [Days rounded up]@row <\/p> You will need to copy\/paste the date into the other rows for this to work, but you can then convert it to a column formula.<\/p> Alternatively:<\/p> =Date$1 + [Days rounded up]@row<\/p> (change the 1 for the row number of your date). Here you can't convert to a column formula, but you can copy\/paste or drag it down.<\/p> Pick whichever option you prefer! 🙂<\/span><\/p> Hope this helps, if there are any problems\/questions then just post!<\/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":112007,"type":"question","name":"How to count number of rows where a date in a column is equal to a specific date?","excerpt":"Hi all, I am trying to create a time series line chart to show the progress of issues put in a specific status every day. On our Issue log, we have date column which captures the date on which an issue was set to either Rejected, Closed or Acceptable Difference. Created a supporting sheet to map the data for the chart…","snippet":"Hi all, I am trying to create a time series line chart to show the progress of issues put in a specific status every day. On our Issue log, we have date column which captures the…","categoryID":322,"dateInserted":"2023-10-23T12:26:06+00:00","dateUpdated":null,"dateLastComment":"2023-10-23T22:03:34+00:00","insertUserID":158518,"insertUser":{"userID":158518,"name":"svijay22","url":"https:\/\/community.smartsheet.com\/profile\/svijay22","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T16:39:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167272,"lastUser":{"userID":167272,"name":"LesahDee","url":"https:\/\/community.smartsheet.com\/profile\/LesahDee","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T22:11:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":33,"score":null,"hot":3396165580,"url":"https:\/\/community.smartsheet.com\/discussion\/112007\/how-to-count-number-of-rows-where-a-date-in-a-column-is-equal-to-a-specific-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112007\/how-to-count-number-of-rows-where-a-date-in-a-column-is-equal-to-a-specific-date","format":"Rich","lastPost":{"discussionID":112007,"commentID":401256,"name":"Re: How to count number of rows where a date in a column is equal to a specific date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401256#Comment_401256","dateInserted":"2023-10-23T22:03:34+00:00","insertUserID":167272,"insertUser":{"userID":167272,"name":"LesahDee","url":"https:\/\/community.smartsheet.com\/profile\/LesahDee","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T22:11:28+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\/LNQ0MPSG5UUE\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/LNQ0MPSG5UUE\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/LNQ0MPSG5UUE\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/LNQ0MPSG5UUE\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/LNQ0MPSG5UUE\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/LNQ0MPSG5UUE\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-23T20:26:46+00:00","dateAnswered":"2023-10-23T13:45:58+00:00","acceptedAnswers":[{"commentID":401139,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/PU0IN9AWG2BF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/JMMGT3TAV66Z\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
