Averaging a column based on an IF formula

D. Buchta

I have a column with an IF formula looking at a form input of yes/no. If yes, it generates a 5, and if no, it generates a 1. I am trying to average this column in another sheet but keep getting a: #DIVIDE BY ZERO error. I have ensured that I have no error messages in the original column. I've tried other averages off the original sheet using 1-5s that are direct inputs with success, but it seems like this IF formula may not be seen as numerics in the system. Any ideas?

Devin Lee

One option could be to use Avg/Collect to ensure you are getting the right data set.

=AVG(COLLECT([1-5]:[1-5], [Yes/No]:[Yes/No], OR(@cell = "Yes", @cell = "No")))

=AVG(COLLECT({Ref 1}, {Ref 2}, OR(@cell = "Yes", @cell = "No")))