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!

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…