Hi all! Iโm having some issues with an expression. I have a checklist with 16 different columns that can have a status of either incomplete or complete. If any one of the 16 columns have a status of complete in a single row, I want to use an app formula to show a percentage of 6.25 in the percentage column of that row. If any 2 columns of the 16 columns have a status of complete in a single row, I want to use an app formula to show a percentage of 12.5 in the percentage column of that row. Iโm able to get it to work if I write a formula that follows the checklist in a linear fashion, ie. if the first item in the checklist is complete and the subsequent 15 are not, then change percentage column. But I havenโt had luck making it work with any combination of the 16 columns. Can anyone point me in the right direction? Thanks in advance!
Solved! Go to Solution.
Something like this?
(
(
0
+ IFS(("complete" = [column-1]), 1)
+ IFS(("complete" = [column-2]), 1)
+ ...
+ IFS(("complete" = [column-N]), 1)
)
* (100.0 / N)
)
Replace column-1
(etc.) with the names of the columns, and N
with the total number of columns.
Something like this?
(
(
0
+ IFS(("complete" = [column-1]), 1)
+ IFS(("complete" = [column-2]), 1)
+ ...
+ IFS(("complete" = [column-N]), 1)
)
* (100.0 / N)
)
Replace column-1
(etc.) with the names of the columns, and N
with the total number of columns.
Another variation may the use of SUBSTITUTE(), EXTRACT() ETC.
COUNT(EXTRACTHASHTAGS(SUBSTITUTE(LIST([Q1 OK] , [Q2 OK], [Q3 OK], [Q4 OK] , [Q5 OK]โฆ[Q16 Ok]), โCompleteโ, โ#Zโ)))*6.25
Nice!
Thanks @Steve and @Suvrutt_Gurjar. Iโll try these and let you know how it goes. Appreciate the education as always!
User | Count |
---|---|
42 | |
34 | |
27 | |
23 | |
16 |