AVERAGEIF function help?
![Kate Allison](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I'm having trouble with the AVERAGEIF function. I have a project intake sheet, and I'd liek to take the average of a number among all projects in the sheet.
I'm attempting to use =AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0)), but I can't figure out how to protray the critera correctly to make the formula work.
I want it to only take the average of cells in Duration of Hold, but only the ones that have a number. The column Duration of Hold is calculated based on Start and End date columns. So some rows have a number, but in others the data is still incomplete so some cells say "#INVALID DATA TYPE" because the project hasn't progressed yet. My AVERAGEIF formula is coming back as "UNPARSABLE" currently.
Is there a way to exclude the INVALID DATA TYPE rows in my AVERAGEIF formula? See screenshots below for reference. Thank you!
Best Answer
-
Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.
=IFERROR(your formula),"")
THEN you can do
=AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
Hey @Kate Allison,
If you wrap your formula in an IFERROR function, that might remove the invalid data errors:
=IFERROR(AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0), "")
See if that works!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
what if you change the > 0 to <>””
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@bisaacs It did not work :( It no longer has an error, but now it's just returning a blank cell. It should return a 5 based on the data currently in the sheet.
@Mark.poole changing to <>"" also did not work and still returned #UNPARSABLE.
Maybe there's a way to incorporate ISNUMBER? I haven't figured it out so far this morning.
-
Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.
=IFERROR(your formula),"")
THEN you can do
=AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole adding in IFERROR worked - thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!