Forum Discussion
Using checkboxes to create percentage bar
I've seen that you can use Excel to create a percentage bar when using checkboxes. However, is there a way to assign a specific percentage to each check box rather than dividing it by the total number of boxes?
We work on projects and some tasks take a longer amount of time to complete than other tasks and as such may account for 60%, the next, say 20% and a further two boxes each 10%, for example. Is this possible to achieve, even if it means using hidden cells or figures on another sheet within the workbook?
Thanks, Craig
Including the values on the worksheet to give an alternating pattern is a step backward because it makes any calculation harder. The formula I would use is
= BYROW(status * {60,20,10,10,10}%, LAMBDA(x, SUM(x)))which spills to return the entire column of overall percentages.
[Look out for the new checkboxes when the come available; they are much better to set up and use]
8 Replies
Including the values on the worksheet to give an alternating pattern is a step backward because it makes any calculation harder. The formula I would use is
= BYROW(status * {60,20,10,10,10}%, LAMBDA(x, SUM(x)))which spills to return the entire column of overall percentages.
[Look out for the new checkboxes when the come available; they are much better to set up and use]
PeterBartholomew1 that's fantastic thanks. I did manage to make it work by putting in the values in the cell adjacent, however, now its in my spreadsheet I can see exactly what you mean and its a really clean way of doing it so I'll be updating the sheets with now.
I am pleased that the ideas may be of some value to you. If you miss the information provided by the percentage weightings you could insert them as a range, possibly above the headings. Were you to do that, you could even reference the values from that range in place of using the array constant as I did.
Is this the sort of thing you require?
This is just a conditional format, but maybe you have some other strategy for your progress bar?
My checkboxes simply return TRUE or FALSE but the percentage is calculated in the formatted cell using
= SUM(checkMarks * {60;20;10;10}%)PeterBartholomew1 Thanks.
I've attached a copy of what it is that I'm working with as we literally have hundred of rows for some projects and as such struggled to implement your suggestion, unless I'm missing something? I've now added the percentages in the cells adjacent for my reference and also to possibly assist with a formula?
