# Checked box formula

1. ## Checked box formula

Hey ya'll!

I wouldn't say i'm a beginner, but with a new job, i'm venturing into using Excel more. I'm trying to create a formula for when i check a box, i can add a weight to it when it's checked, to come up with an average for an overall category. Can someone help?

2. ## Re: Checked box formula

Your example showed a bunch of checkboxes but not how you want to apply a weight and average an overall category.

Here is the overall strategy:

For each checkbox, right-click, then select Format Control, and click Control tab. In Cell link, put the address of a cell that will hold the state of the checkbox (TRUE or FALSE) (A1 in this example).
Have another cell with the weight (B1 in this example)
Now for each checkbox, you will have a formula that gives the weighted value of that checkbox, referring to the linked cell. For this example the formula is in C1.

=IF(A1,B1,0)

Then you have a formula to sum or average the results of all these formulas. You seem to be using a percentage, which is not a weighted average, so I'm not clear on what you need. But a percentage would be something like

=SUM(A1,A2,A3,A4,A5)/SUM(B1,B2,B3,B4,B5)

If this does not seem to answer your question then provide some additional information about your file. For example, Validation has a percentage of 85% but no explanation of how it was calculated. It seems to be a percentage of the number of boxes checked, not any kind of weight or average.

3. ## Re: Checked box formula

I realize I didn't give enough detail, my apologies.

For instance, the QC1 Batch category has 3 boxes (No checks left in folder, no prepaids left in folder, etc.). I'd like them all, when checked, to have an average of 100%, I guess giving each checked box 33.33% in weight. Forgive me if using wrong term.
If one of those 3 boxes aren't checked, it would automatically total to be 66% since only 2 of the 3 boxes are checked.

Just to explain the 89% for Validation was me manually giving each box (9 of them) an 11% weight, so it'd equal close to 100%.

4. ## Re: Checked box formula

I applied Jeff's proposal to the QC1 section. (hopefully I got it right).
1. The check box's in C24:C26 are linked to cells J24:J26
Note that linking check boxes is rather tedious. If I had a choice I would go with T/F dropdowns instead.
Note that the TRUE/FALSE in J24:J26 can be hidden by changing the font to white.
2. The formula in cell B23 is: =COUNTIFS(J24:J26,TRUE)/3
Let us know if you have any questions.

5. ## Re: Checked box formula

Originally Posted by Rebel17
I'd like them all, when checked, to have an average of 100%, I guess giving each checked box 33.33% in weight. Forgive me if using wrong term.
If one of those 3 boxes aren't checked, it would automatically total to be 66% since only 2 of the 3 boxes are checked.
So what you want is simply the percentage of boxes checked. No need to references weights or averages. You are just taking (number of boxes checked) / (total number of boxes)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1