Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Average If Formula
Is there such a thing as an Average If Formula in smartsheet? so to average only cells that meet a certain criteria?
Comments
-
Shaine Greenwood EmployeeHello—
We don't have an Average IF formula in Smartsheet but I've got your vote down for this on our enhancement request list for further consideration.
With being said, you can use a SUMIF formula to sum the values that meet your conditions, then divide it by a COUNTIF formula of the values that meet your conditions.
=SUMIF(...)/COUNTIF(...)
More on formulas here:https://help.smartsheet.com/articles/775363-using-formulas
-
Ally ✭
Hello,
Just checking in to see if this functionality has been created yet? It would be very useful for my purposes, as well.
Thanks!
Ally
-
I too would like to use an averageIF.
-
ggross ✭
It has been about a year and a half since this was first reported/requested. Do we have an idea of when this is going to be added? AverageIF is a pretty common formula for PMs and would be a great asset for dashboard reporting. Is it possible to get this prioritized in a near term release?
-
J. Craig Williams ✭✭✭✭✭✭You can use =AVG(COLLECT(range to sum, criteria range, criterion)
The intent, I believe, of COLLECT, is to avoid have special use case functions when one tool can satisfy multiple ones.
Craig
-
If the AverageIF formula still being worked on? This would be a great feature to have.
-
J. Craig Williams ✭✭✭✭✭✭
See my post below on AVG(COLLECT()).
My experience is that COLLECT()'s flexibility allows functions like AVERAGEIF to be created. This is NOT a work-around, but a design choice and a good one.
My belief is that you will never see AVERAGEIF() as a stand-alone function.
我唯一能看到的是用例移植of existing Excel / OpenOffice spreadsheets into Smartsheet, and that is an import problem, not a function problem.
Craig
-
Yep - this function would still be useful.
(The problem with the Collect function is that it does not automatically adjust when columns are added).
-
RJHola ✭✭
时的平均数据,并试图克雷亚te dashboards, you need this function to create the dashboard views at a filtered level.
If not, pivot tables to filter would truly be easier to build dashboards.
-
Craig,
Do have a way to use this formula across sheets when you're trying to get the average of a child rows based on certain criteria? I got an #UNSUPPORTED CROSS-SHEET FORMULA error I think because children function is not supported.
Right now, I just added a helper column and linked the cell in the other sheet to the help column cell. However, I'd like to avoid that if possible.
Thanks,
Brett
-
J. Craig Williams ✭✭✭✭✭✭
Brett,
On the source sheet, you will need to have some way to determine the rows that are CHILDREN.
That additional range and criterion to the COLLECT's list of range/criteria on the destination sheet.
It is one more step and may clutter up the source sheet, may be additional admin burden when multiple source sheets are involved, but since COLLECT does not accept the hierarchy functions, there isn't really a viable alternative.
Craig
-
Kara Lumley EmployeeHello everyone,
AverageIf is in the works, along with some other new functions. Please note that the timeline we are targeting for release (July) is subject to change and is not guaranteed.
Thank you,
Kara
-
Andrée Starå ✭✭✭✭✭✭
Excellent news Kara!
Can't wait!
Have a fantastic weekend!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Add my vote to add this formula into SS!!
Categories
How exactly is your Data Shuttle set up? If it is set to replace everything in the sheet when it runs, it is deleting the cell being referenced by the widget and then putting new data in new cells.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":108271,"type":"question","name":"Conditional formula - if a date is less than 30 days from today","excerpt":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), \"Less than 30 days from today\", \"More than 30 days from today\") Help?","snippet":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula.…","categoryID":322,"dateInserted":"2023-07-28T18:21:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":159884,"lastUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":24,"score":null,"hot":3381143059,"url":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","format":"Rich","lastPost":{"discussionID":108271,"commentID":387894,"name":"Re: Conditional formula - if a date is less than 30 days from today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387894#Comment_387894","dateInserted":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+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-07-28T19:21:56+00:00","dateAnswered":"2023-07-28T18:55:42+00:00","acceptedAnswers":[{"commentID":387890,"body":"
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"\")<\/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&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
