Forum Discussion
Using checkboxes to create percentage bar
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]
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.
- I have created the sheets for a few projects now and formulas work very well, however a couple of issues I've had is the work sheets running quite slowly due to the number of columns and rows we need, but I kind of got around this by having separate sheets rather than workbooks. The second issue is that most users will be onsite using a phone or tablet to complete the sheets and I've found that we cant use Form Control on mobile devices.
Another user has suggested using Yes/No pull down as this eliminates Form Control, then use Card View to make it more compatible. I'm happy with this, but the formula you assisted me with isn't going to work with this. Therefore, can you suggest a formula that essentially does the same thing, but works when yes is selected?
