Get Average of "checkboxes checked" for an entire column reference from another sheet
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
Best Answer
-
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.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
Answers
-
Editing because I thought of something different. Old post blocked out. New info below.
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
=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
...
-
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
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff -
Thanks for your continued advice...
For example, below should be 50%
And below should be 100%
-
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.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
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!
-
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"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!