Get Average of "checkboxes checked" for an entire column reference from another sheet

Danny Lam

Hi Everyone -

I'm trying to get an average of "checkboxes checked" for an entire column range. I came up below but it's not working.

=COUNTIF({1 Main Speaker System Range 2}, 1 / COUNT({1 Main Speaker System Range 2}))

I was successful to get an average of a row range because I divided by a fixed number. Here is the formula...

=COUNTIF(Cabling@row:[AV Test]@row, true) / (5)

But I can't seem to get it to work for an entire column. Note the number of rows will change as more data is added, so I can just divide by a certain number

Kevin Smith

Hi,

Here is what I have working for me:

I first get the count of checked boxes with:

=COUNTIF({Grid of Checkboxes Range 2}, 1)

This is a range of just the column with the checkboxes I am averaging.

Then I get a count of the rows so I need a new range.

=COUNT({Grid of Checkboxes Range 4}) This will work best for the Primary col. or a col that you know will have a value for each row. Without that, it would take a solid way to know that you have a row to include in the calculation.

Then you divide the first by the second.

=COUNTIF({Grid of Checkboxes Range 2}, 1) / COUNT({Grid of Checkboxes Range 4})

You can multiply again if need it in another format other than .5.

If that does not work for you please let me know.

Jeff Reisman

@Danny Lam

Editing because I thought of something different. Old post blocked out. New info below.

Spoiler Warning

This might work for you. Add a helper column, use a formula like =1 and make it a column formula. This will load the whole column with the number 1.

Then you divide your count of checked boxes in the checkbox range by the count of populated cells in your helper column.

=COUNTIF({1 Main Speaker System Range 2}, 1) / COUNTIF({1 Main Speaker System Helper range}, 1)

=COUNTIF({1 Main Speaker System Range 2}, 1) / (COUNTIF({1 Main Speaker System Range 2}, 1) + COUNTIF({1 Main Speaker System Range 2}, 0))

In English: Count the number of checked boxes, divide by the the sum of the count of checked boxes and the count of unchecked boxes.

heyjay

=COUNTIF({1 Main Speaker System Range 2}, 1 / SUM(COUNTIF({1 Main Speaker System Range 2}, 1), COUNTIF({1 Main Speaker System Range 2}, 0))

The denominator will be the total number or rows, with or without boxes

1 = checked box

0 = unchecked box + row without box

Danny Lam

Jeff -

Thanks for contributing a solution, unfortunately it didn't yield accurate results. I tried both formulas, but it didn't produce a "correct" average. I'm going back to the drawing board.

Everyone -

If anyone would like to take a crack at this, I would appreciate it. Thanks!

-Danny

Jeff Reisman

@Danny Lam To be honest, I'm not sure what kind of "correct" average you were expecting with this ask. A checkbox is either a 1 or a 0. Any "average" you take of these in a column will always just be "what fraction of the total checkboxes in this column are checked?"

Now, let's say you have 5 columns of checkboxes, and you want to find out what is the average number of checked boxes per row. That we can certainly do. Is this what you're actually looking for?

If not, can you include a screenshot of your column layout and a some of the data? That may help us find what you are looking for.

Danny Lam

Jeff -

Thanks for your continued advice...

For example, below should be 50%

And below should be 100%

Kevin Smith

Hi,

Here is what I have working for me:

I first get the count of checked boxes with:

=COUNTIF({Grid of Checkboxes Range 2}, 1)

This is a range of just the column with the checkboxes I am averaging.

Then I get a count of the rows so I need a new range.

=COUNT({Grid of Checkboxes Range 4}) This will work best for the Primary col. or a col that you know will have a value for each row. Without that, it would take a solid way to know that you have a row to include in the calculation.

Then you divide the first by the second.

=COUNTIF({Grid of Checkboxes Range 2}, 1) / COUNT({Grid of Checkboxes Range 4})

You can multiply again if need it in another format other than .5.

If that does not work for you please let me know.

Danny Lam

Kevin -

Thank You! Your idea worked. I created 2 helper columns in the destination sheet. One with "checked" checkbox range. And another one with "data" in the primary column range. Then I just created simple division formula within the same destination sheet...

=[Cabling Helper Column]@row / [Primary Helper Column]@row

Once I populate all data, I will hide and lock the column.

Thanks again!

Jeff Reisman

@Danny Lam

Ok so you were just after percentage checked. You don't need helper columns, that's what my formula gave you:

Total checkboxes checked, divided by (the total number of checked boxes + the total number of unchecked boxes)

If you have 36 rows with checked boxes, and 39 rows with unchecked boxes, then

36 / (36 + 39) = 36/75 = .48

.48 in a percent-formatted column would show as 48%. Or, if you need it in a regular text/number field, just multiply the formula by 100 and add a percent symbol to the end:

=(here's your formula * 100) + "%"

• Note: the above would turn the numeric result into a percent value stored as text, just FYI.

If there is other criteria you want to add to specify which rows to count, that's doable as part of the formula. Let's say you want to only count rows where the Device = "Projector". We change the COUNTIF to a COUNTIFS so we can add the extra criteria:

=COUNTIFS({1 Main Speaker System Range 2}, 1, {1 Main Speaker System Device Col Range}, "Projector") / (COUNTIFS({1 Main Speaker System Range 2}, 1, {1 Main Speaker System Device Col Range}, "Projector") + COUNTIFS({1 Main Speaker System Range 2}, 0, {1 Main Speaker System Device Col Range}, "Projector"))

Danny Lam

Jeff -

Thanks for your continued advice. I will try it as well. You have help to increase my overall "functions" and "syntax" knowledge for other scenarios. Thank You!