If criteria from 2 columns is met, pull the avg of a third column

sammmm

I need the avg of the "% to complete" column if the "project" column is checked and the "Law" column = 1. It keeps giving a divide by zero error. When I tried it with count instead of avg, it is only showing a count of 1 even though many rows meet the criteria.

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, =1, Law:Law, =1))

Thanks!

Janae G.

Your formula looks pretty good, I think the one thing that might be throwing the error is that when you have a criterion that looks at a checkbox column, you don't need the equals sign before the 1 or 0.

Try this:

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, 1, Law:Law, =1))

Hope this helps!

sammmm

Unfortunately, it is still showing a divide by zero error.

VBAGuru

You need to remove the second '=' sign as well.

sammmm

Still receiving the same error.

jmyzk_cloudsmart_jp

Hi @sammmm

As I tested, those formulas work.

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, true, Law:Law, 1))

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, true, Law:Law, 1))

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, @cell = 1, Law:Law, =1))

The closest formula to yours is the last one, which I put @cell in front of "=1".

=AVG(COLLECT([% To Goal]:[% To Goal], Project:Project, =1, Law:Law, =1))

As you commented, removing "=" from the two "=1" did not work. I guess this is how Smartsheet checkbox boolean column works.

https://app.smartsheet.com/b/publish?EQBCT=eef323a65b8e45d7966909b506f91c32