I'm looking to set up a formula that will show % completed based on checkboxes

dhuff11

I'm looking to set up a formula that will show % based on checked/unchecked boxes. For example, in 1 column, I have 1 checked box, and 1 unchecked box. I am looking for it to say 50% are checked. The difficulty lies in the fact that it's information being pulled from another sheet, and it needs to be set within a certain date range

MichaelTCA

The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.

Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.

What are you using to calculate the total amount?

MichaelTCA

Hello @dhuff11

You could try using the COUNTIFS() function to enter multiple criteria such as whether its checked and within a certain time frame.

Then use the COUNT() function to get a count on the entire range of values.

Then divide the result by the total and get the percentage.

dhuff11

The issue I face with that formula is the scale of what it's going to be used for. This formula will be used across multiple cells and will be adjusted to each cell. I would end up with a massive amount of columns for each main column I'm trying to get a percentage from. Is there a way for it to be done all in 1 formula? The formula I have currently is:

=AVG(COUNTIFS({Work Center}, "Department", {HRxHR}, 1, {Date}, AND(@cell > DATE(2023, 7, 15), @cell < DATE(2023, 7, 23)), {Shift}, "First"))

HRxHR is the column with the checkboxes I'm trying to get the percentage of. This formula was able to return the correct quantity of checked boxes, but not the percentage compared to unchecked. Instead of showing 1, it should show 50%.

MichaelTCA

The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.

Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.

What are you using to calculate the total amount?

Rachel AR

I actually needed this for a sheet.

Instead of checkboxes we have the symbol (counting "blue"). But this would work for checkboxes also.

I added a column to sum how many were checked over a series of columns.

=COUNTIFS([BG PDF / Ver]@row:[AHA Status / Ver]@row, "blue")

Then used that sum to calculate the % complete.

Hope this helps.

dhuff11

Thank you all, this has been solved. I ended up having to create some helper columns and it helped tremendously.