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!
Something like this?
( ( 0 + IFS(("complete" = [column-1]), 1) + IFS(("complete" = [column-2]), 1) + ... + IFS(("complete" = [column-N]), 1) ) * (100.0 / N) )
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