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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!