Survey completness problems in Google Sheets-CodePudding

Hi every one I'm trying to have information about the completness in some survey results exported in to a Excel Format I'm using Google Sheets, as every survey there is questions and subquestions the subquestions have a conditional, Example: 4. How are you today? multiple choise answers: Good, Bad, Prefer not to say, so there we have 3 answer options if we click good there is a conditional and the subquestion will be: Why?. So in my survey there is 8 questions and question 4, 7 and 8 has conditional questions if someone answer "Yes". Now here is my problem to calculate the percentage of completness I used this relation: number of inputs in the survey/number of expected answers, But as I mentioned before the conditional affects this expected answers this Variable is dynamic depending on the answers from question 4, 7 and 8. So I would like to obtain this Variable for every case, if someone put information will have an ID if we have 20 persons doing the survey we will have 20 ID's. So for every record of answers the number will change depending on the inputs from Question 4, 7 and 8. I have prepared a document in Google sheets will the full aproach that I tried but is still hard to have it right I would like to have some help with this.

CodePudding user response：

if any parts of MAIN or SUB question make a whole MAIN/SUB question count as 1 use:

QUERY(TRANSPOSE(B2:F12),,9^9)

if all parts of MAIN/SUB question count as 1 use regular range:

AS2:BB12

change those ranges if I got them wrong:

=ARRAYFORMULA(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1))

to sum this up there are 17 queries and 2 regular ranges with 12 columns eg 17 12 = 29:

=ARRAYFORMULA(MMULT(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1), 
 SEQUENCE(29, 1, 1, 0)))

now to skip SUB question if empty we can do:

=ARRAYFORMULA(IF(""=TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(AS2:BB12),,9^9)})), 1, 0))

and then:

again, if you got more to skip add it like:

so the last step is to get the "% completeness":

=ARRAYFORMULA(MMULT(IF(""={TRANSPOSE(TRIM({
 QUERY(TRANSPOSE(B2:F12),,9^9);   QUERY(TRANSPOSE(G2:X12),,9^9);   QUERY(TRANSPOSE(Y2:AH12),,9^9); 
 QUERY(TRANSPOSE(AI2:AR12),,9^9); QUERY(TRANSPOSE(BC2:BD12),,9^9); QUERY(TRANSPOSE(BG2:BH12),,9^9); 
 QUERY(TRANSPOSE(BI2:BR12),,9^9); QUERY(TRANSPOSE(BS2:BU12),,9^9); QUERY(TRANSPOSE(BV2:BX12),,9^9); 
 QUERY(TRANSPOSE(BY2:CA12),,9^9); QUERY(TRANSPOSE(CB2:CD12),,9^9); QUERY(TRANSPOSE(CE2:CG12),,9^9); 
 QUERY(TRANSPOSE(CH2:CJ12),,9^9); QUERY(TRANSPOSE(CK2:CM12),,9^9); QUERY(TRANSPOSE(CN2:CP12),,9^9); 
 QUERY(TRANSPOSE(CQ2:CS12),,9^9); QUERY(TRANSPOSE(CT2:CV12),,9^9)})), AS2:BB12, BE2:BF12}, 0, 1), 
 SEQUENCE(29, 1, 1, 0))/(29-IF(""=TRANSPOSE(TRIM({QUERY(TRANSPOSE(AS2:BB12),,9^9)})), 1, 0)))