Search code examples
if-statementgoogle-sheetsconditional-statementsgoogle-sheets-formulagoogle-query-language

Survey completness problems in Google Sheets

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.

Link to the spreesheet

Here is an image about it:

enter image description here

enter image description here

enter image description here

enter image description here

Solution

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

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

    enter image description here

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

    AS2:BB12

    enter image description here

    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)))

    enter image description here

    now to skip SUB question if empty we can do:

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

    enter image description here

    and then:

    enter image description here

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

    enter image description here

    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)))

    enter image description here