AVG a column, Collect the data for a specific employee, yet needing to exclude zero

Billie Jean

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

Jeff Reisman

@Billie Jean

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.

lmlearning

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

Jeff Reisman

@lmlearning

This will ignore both blanks and zeros.

lmlearning

@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.

Jeff Reisman

@lmlearning

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:

lmlearning

@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!