+ Reply to Thread
Results 1 to 5 of 5

Checked box formula

  1. #1
    Registered User
    Join Date
    04-08-2021
    Location
    San Antonio
    MS-Off Ver
    365
    Posts
    4

    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?
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,585

    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-08-2021
    Location
    San Antonio
    MS-Off Ver
    365
    Posts
    4

    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%.

    Look forward to your reply!!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,812

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,585

    Re: Checked box formula

    Quote Originally Posted by Rebel17 View Post
    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how can I activate a formula if checkbox is checked
    By s7yzrs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2019, 08:17 PM
  2. [SOLVED] formula for grade based on checked cells
    By deepanc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-30-2014, 09:48 AM
  3. Check Box that shows options in Combo Box if Checked, and Hides if Not Checked
    By dumbjodie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-13-2013, 10:06 AM
  4. [SOLVED] When checkbox is checked it does not copy to new sheet. Only if not checked using false
    By thelisa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2013, 07:59 AM
  5. Replies: 2
    Last Post: 12-19-2012, 11:23 PM
  6. [SOLVED] Excel 2002 formula displayed not value formula option not checked
    By Dean in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2006, 10:35 AM
  7. Replies: 1
    Last Post: 12-17-2005, 05:56 PM

Bookmarks

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