Forum Question: Please Help Using If Statements and Checkboxes

I am trying to use numbers with checkboxes showing a percentage of how may checkboxes are marked.I have watched your tutorial on if statements and checkboxes but Im missing something. (besides not being confident with formulas). I am using the formula =IF(R5,1/26*100%,R5)in cell L3. There are 26 checkboxes from cell R5 to R30. So the first checkbox when ticked shows 3.85% all good. what I can’t seem to do is copy this cell to the remaining cells and other checkboxes, the percentage does not change when the checkbox is marked. If I use =IF(R5:R30,1/26*100%,R5)an error comes up saying the range R5:R30 cant be used as a single value or when trying other things I get a Syntax error. Hope you can follow Im getting confused writing!!!
Regards Troy
—–
Troy De Laine

Comments: 2 Responses to “Please Help Using If Statements and Checkboxes”

    2/11/13 @ 5:28 pm

    I’m not quite sure I understand how you are trying to do this. But I understand the problem, and the way I would solve it is to use the COUNTIF function.
    So if you have checkboxes in cells R5 to R30, then this formula will count the number of them that are checked:
    =COUNTIF(R5:R30,”=TRUE”)
    If you want to express that as a percent, then divide by 26 and format the cell as %.
    Or, to avoid having a fixed number like 26 in the formula, use COUNTA to get the total range. This gives the the number of non-empty cells in a range.
    =COUNTIF(R5:R30,”=TRUE”)/COUNTA(R5:R30)

    Troy
    2/11/13 @ 5:48 pm

    Thank you Gary

    The =COUNTIF(R5:R30,”=TRUE”)/COUNTA(R5:R30) has worked for me. Just goes to show I don’t know what is going on and there is a lot to learn.

    I do appreciate your help
    This site and your knowledge has helped me so much

    Thanks again Troy

Comments Closed.