Excluding "0" value from average calculations

Hey everyone! I was wondering if any one had a tip on how to exclude values of 0 from getting an average in a column? For example, our team is conducting Q+A for our associates and every new row of our table will create a QA score of 0% without any data entered. If Associate A has a QA score of 95, Associate B has a QA score of 85 but Associate C’s row has been accidentally created with no data and have a score of 0. Thus, our running average comes out to 60 because it takes into account the score of 0. Of course we will be training and coaching our managers to avoid this but human error is inevitable it seems. If there was a way to exclude any 0 values from this running average that would be the best!

Thank you

Jordan

You can create a canvas formula that would sum all values greater than 0 and then divide by the count of those values…

[My Table].Filter([QA Score] > 0).[QA Score].Sum() / [My Table].Filter([QA Score] > 0).Count()

This is great thanks @BenLee! This helps in keeping an accurate running total of the average score. I had initially added this formula as column in our table, and I was hoping that it would show the same score but it does not.

The rows/columns in pink are the scores from each Q+A review. The rows/columns in blue are using the formula you provided which is awesome but ideally they would still show the same scores as the pink section.

This is what it looks like right now for us in Airtable

Would you have any recommendations? Maybe it’s best to not use this formula in it’s own column? Also, I connected with Lauren Struthers today who was signing your praises! Thanks for your help

The way I have the formula written in the post above is for a canvas formula. This will take all the values of a column and average them. If you need to average the cell’s in a row, which it looks like you’re doing, then you’ll need to write the column formula a little different.

So “Total QA Score” is the average of “Professionalism Score” and “Accuracy Score” for only that row.

For the “Total QA Score” formula, I’d look to something like this…

List([Professionalism Score], [Accuracy Score]).Filter(CurrentValue != 0).Sum() / List([Professionalism Score], [Accuracy Score]).Filter(CurrentValue != 0).Count()

If you have an example doc to share, that always helps so we can see exactly what’s going on. But based on the screenshots, I think this is what you’re after and will avoid the issue of a value in either column being 0.

@BenLee This is awesome, Ben! Thank you. I just used this formula in our doc and all signs point to this being the solution we were looking for. Thanks again for all your help and i will pop back in if i’m still having trouble

  • Jordan
1 Like