Having troubles with percentage column

[Wallace_Service]

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!

[Steve · Accepted 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.

[Suvrutt_Gurjar]

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

[Steve]

Nice!

[Wallace_Service]

Thanks @Steve and @Suvrutt_Gurjar. I’ll try these and let you know how it goes. Appreciate the education as always!