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
Check out the Formula Handbook template!