Calculating average difference in dates between two columns
Hi I am trying to find the average time to complete a task without adding additional columns in my sheet. I would like to set it up in the sheet summary for easy reporting.
In excel I would use the following formula. If a cell is blank the row will not be calculated as part of the average.
=SUMPRODUCT((I2:I52)-(E2:E52),--(E2:E52 > 0),--(I2:I52 > 0))/SUMPRODUCT((ISNUMBER(I2:I52))*ISNUMBER(E2:E52))
Best Answer
-
Genevieve P. Employee AdminCan I ask why you don't want to add another column? The only way I can currently think of to achieve this is to add one additional column to your sheet...this would be needed to calculate the days-per-row using your two date columns. If you just didn't want the clutter of another column you could thenhidethe column, and it would still work for your calculations. The formula to find the days in each row is pretty simple:
=[CCP NOTICE]@row - [ASSIGNED DATE]@row
Then you could buildthe AVERAGEIF formulato average the days from the helper column. The formula I have in the screen capture below is as follows:
=AVERAGEIF([Days to Fill]:[Days to Fill], NOT(ISBLANK(@cell)))
Or in your sheet,
=AVERAGEIF([Helper Column]:[Helper Column], NOT(ISBLANK(@cell)))
Let me know if this would work for you!
Cheers,
Genevieve
Answers
-
Genevieve P. Employee Admin
Can I ask why you don't want to add another column? The only way I can currently think of to achieve this is to add one additional column to your sheet...this would be needed to calculate the days-per-row using your two date columns. If you just didn't want the clutter of another column you could thenhidethe column, and it would still work for your calculations. The formula to find the days in each row is pretty simple:
=[CCP NOTICE]@row - [ASSIGNED DATE]@row
Then you could buildthe AVERAGEIF formulato average the days from the helper column. The formula I have in the screen capture below is as follows:
=AVERAGEIF([Days to Fill]:[Days to Fill], NOT(ISBLANK(@cell)))
Or in your sheet,
=AVERAGEIF([Helper Column]:[Helper Column], NOT(ISBLANK(@cell)))
Let me know if this would work for you!
Cheers,
Genevieve
Help Article Resources
Categories
I hope you're well and safe!<\/p>
Try something like this. (I've guessed what your formula would look like, but you'd have to update the column names to match yours)<\/p>
=JOIN(COLLECT([COLUMN2]:[COLUMN2], [COLUMN2]:[COLUMN2], 1, [COLUMN3]:[COLUMN3], \"Support\"), CHAR(10))<\/p>
Did that work\/help? <\/p>
There were a lot of errors in the formula, but it was mainly the brackets { is used for cross-sheet formulas, and you have to close the [ ] square brackets around a column name that doesn't only have one piece of text, and then the structure wasn't correct.<\/em><\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106973,"type":"question","name":"SUMIFS with CONTAINS","excerpt":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, \"CF\", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…","snippet":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column.…","categoryID":322,"dateInserted":"2023-06-27T19:19:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T12:30:22+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+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-28T13:14:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375850768,"url":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106973,"commentID":382803,"name":"Re: SUMIFS with CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382803#Comment_382803","dateInserted":"2023-06-28T12:30:22+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-28T13:14:57+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-28T09:44:38+00:00","dateAnswered":"2023-06-27T19:47:23+00:00","acceptedAnswers":[{"commentID":382674,"body":" Try this:<\/p> =SUMIFS({Ext Price}, {Activity Description}, CONTAINS(\"CF\", @cell))<\/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":106971,"type":"question","name":"Using SUMIFS Formula","excerpt":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!","snippet":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of…","categoryID":322,"dateInserted":"2023-06-27T18:45:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T12:24:31+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T12:47:35+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-28T13:14:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":36,"score":null,"hot":3375849593,"url":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106971,"commentID":382799,"name":"Re: Using SUMIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382799#Comment_382799","dateInserted":"2023-06-28T12:24:31+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-28T13:14:57+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-27T20:19:39+00:00","dateAnswered":"2023-06-27T19:59:16+00:00","acceptedAnswers":[{"commentID":382688,"body":" Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p> When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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"}]}],"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":[]}">
<\/p>
