Average of Boxes Checked in Column
Hello all!
I have a column of check boxes in which I'm trying to find the average number of boxes checked in a range.
I'm trying to use the COLLECT function, w/this formula:
=AVG(COLLECT([Photography Needed]2:[Photography Needed]5, [Photography Needed]2:[Photography Needed]5, 1))
When I do so, I receive the error =DIVIDE BY ZERO.
Can anyone shed some light on what I'm doing wrong?
Thanks in advance!
Comments

Hi Paul! This looks fantastic  thank you! However, when I use it, I get the #BOOLEAN EXPECTED error. Any thoughts on how I might avoid this?

UPDATE: adding + "" after the formula returned a numerical response. All set! Thanks again, Paul!

Sure thing. Happy to help!
Keep in mind though... Using the +"" to get it to display is actually converting it from a numerical value to a text string. If you are going to reference that cell within another formula where it needs to be a numerical value, you will need to incorporate the VALUE function.
For example,
=IF([Column Name]1 > 0, "Cool", "Not Cool")
will always return "Not Cool" because it is looking at a text string that just appears to be a number. To get it to register as a numerical value, it would look like this.
=IF(VALUE([Column Name]1) > 0, "Cool", "Not Cool")

Hi Paul,
I'm having some trouble finding the right placement for the VALUE function within my formula. Can you help steer me in the right direction? TY!

Wherever the text value is that you are trying to convert to a numerical value.
For instance, if the cell at [Column Name]@row contains a text value representing a number (say a formula that returns a number that you placed a + "" so it will show up in a checkbox), you would use
VALUE([Column Name]@row)

Perfect, thank you!

Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!