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
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 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!