Average of Boxes Checked in Column

Lisa Marie

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!

 

Paul Newcome

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

Lisa Marie

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?

Lisa Marie

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

Paul Newcome

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

Lisa Marie

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!

Paul Newcome

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)

Lisa Marie

Perfect, thank you!

Paul Newcome

Happy to help!