# Average of Boxes Checked in Column

Options
✭✭✭
edited 12/09/19

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?

Tags:

• ✭✭✭✭✭✭
Options

You are going to need to use something along the lines of

=COUNTIFS([Photography Needed]2:[Photography Needed]5, 1) / COUNTIFS([Photography Needed]2:[Photography Needed]5, OR(@cell = false, @cell = true))

• ✭✭✭
Options

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?

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭
Options

Perfect, thank you!

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!