# 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))

• ✭✭✭✭✭✭

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

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

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

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!