Averaging a column based on an IF formula


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!