AVG a column, Collect the data for a specific employee, yet needing to exclude zero
Here is my formula:
=AVG(COLLECT({Bio Validation Completion Time}, {Bio Validation Schedule Scientist}, [Principle Investigatorss]@row))
Which is working; however, i want to exclude zero's. I tried this:
=AVG(COLLECT({Bio Validation Completion Time}, {Bio Validation Schedule Scientist}, [Principle Investigatorss]@row, <>0))
But it is not working. Please help
Answers

How are you calculating the values that you are averaging? If they are calculated, add an IF function that says if the result is zero, leave the cell blank. The AVG function ignores blanks:
The average of blank+5+7+3 is 5.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@Jeff Reisman can you show an example of how you would write the formula to exclude counting the zero value or blank cells?

This will ignore both blanks and zeros.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@Jeff Reisman , For my specific scenario I am referencing another sheet for the values. I have tried the AVERAGEIF but unfortunately it takes into the account the blank cells or cells with zero value. The formula being used is =AVERAGEIF({market}, "Peru", {occu})
I then tried the AVG Collect, but now I am getting a #DIVIDE BY ZERO error message. The formula is the following: =AVG(COLLECT({occu}, {occu}, @cell <> 0, {market}, @cell = "Peru"))
Any ideas how I can use either or something different? to match the country, to the occupancy column, and calculate average of the occupancy of only that country but exclude zeros and blank cells? Thank you in advance.

Your formula is fine, the issue has to be with the data or a broken range reference . However, no matter what I try, I can't recreate your error.
Edit your range references and make sure they're good. Check your ranges for any errors or weird values.
My test:
And my data with all kinds of crap in there:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@Jeff Reisman Just to close the loop on this, you were right on the initial data having errors. I went as far back as I could and the data set had an apostrophe at the beginning of the value, instead of 30.6 it was '30.6. I can only assume maybe when they exported from excel, it carried the apostrophe. It did not show until you clicked into the cell so it had gone unnoticed. Thank you for your help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!