Calculate score on one column based on percentage of answers in another column

Hi ClaudiaTay 

I most likely owe you an apology for this.  I have not so much set out to answer your question (that has been done already) as to use it to evaluate my Excel strategy.  My approach, as usual, is to go for a single array formula with no direct cell references visible.  I haven't addressed some of the refinements to the assessment which would, most likely, to best placed within the Lambda function to avoid the usual nested array issues that somewhat mars the dynamic array functionality.

The basic calculation creates the spilt array to the right of the image using

= LET(
    question?,     ISTEXT(question),
    questionID,    TOCOL(question, 3),
    questionStart, TOCOL(IF(question?, SEQUENCE(ROWS(question)), NA()), 3),
    questionEnd,   VSTACK(DROP(questionStart, 1), 1+ROWS(question)),
    questionParts, questionEnd - questionStart,
    percentYes,    MAP(questionStart, questionParts, ScoreQuestionλ(answers)),
    scoring,       IFS(percentYes=100%, 3, percentYes >= 50%, 2, TRUE, 1),
    HSTACK(questionID, scoring)
  )

where the Lambda function that returns the percentage of  "Y"s is

ScoreQuestionλ
= LAMBDA(ans, [YN], LAMBDA(s,p, 
    COUNTIFS(TAKE(DROP(ans, s-1), p), IF(ISOMITTED(YN),"Y",YN)) / p
  ))

The key is that TAKE returns a range reference (as would OFFSET) so the use of COUNTIFS is possible.  Although the function defaults to counting the occurrences of "Y", it will return counts for other strings.

The distributed scores to the left are extracted from the underlying array results by using XLOOKUP

    ...
    scoring, ...,
    IF(ISTEXT(question), XLOOKUP(question, questionID, scoring), "")
  )

In use, I probably should turn the formula into a documented Lambda function with change control, help, error trapping and the rest, but this will do for the moment.

ClaudiaTay 

You can use pivot table to count the number of Ys and Ns.

Then % can be calculated and if function can be used to find score.

Only limitation of this method is, you have to unmerge the cell and fill down.

refer attached sheet.

ClaudiaTay 

It depends on which version of Excel you are. For the legacy one (not 365) as variant for

it could be

=IF(  $A3 = "", "",
        LOOKUP(
          COUNTIF( OFFSET($A3, 0, 2, MATCH(TRUE, $A4:$A$30<>"",0) ), "Y" ) /
                   MATCH(TRUE, $A4:$A$30<>"",0),
      {-1,0.1,0.5,1},  {0,1,2,3}  )
 )

ClaudiaTay 

=IF(COUNTIFS($A$3:$A$18,A3,$C$3:$C$18,"Y")/COUNTIFS($A$3:$A$18,A3)<=0.5,1,IF(COUNTIFS($A$3:$A$18,A3,$C$3:$C$18,"Y")/COUNTIFS($A$3:$A$18,A3)<1,2,3))

I'd remove the merged cells and use this formula.

ClaudiaTay 

Hi there!

I have come up with a formula that can do that for you. However, it is a bit messy, as it will have you to edit the formula ranges based on the number of statements each question has. If your questionnaire is long, it would be a bit time consuming, but it will serve to automatically count the score if you do not have better and more efficient solutions.

The formula would be like this one:

=IF(COUNTIF(C7:C10;"Y")=4;3;IF(COUNTIF(C7:C10;"N")=4;1;IF((COUNTIF(C7:C10;"Y")/COUNTIF(C7:C10;"N"))<=0,5;1;IF((COUNTIF(C7:C10;"Y")/COUNTIF(C7:C10;"N"))>0,5;2;""))))

The logic behind it is the following:

- First, it checks whether in a range of statements within a question, there are unanimous answers (that is, if these are all Y or all N). In that case, it automatically displays score 3 if each of them are Y and score 1 if each of them are N.

- Then, in case there are no unanimous answers (that is, there are some Y answers and some N answers, it continues with the formula.

- The continuing is basically based on dividing the number of Y answers by the number of N answers. Based on the result of the division, it applies the score based on what you have decided on your post.

Drawback of this solution:

You will have to edit the formula and change each ranges it selects depending on the number of statements each question has.

Also, you will have to change the values "4" you find at the beginning of the formula (you find them two times) based on the number of statements each question has.

For instance, based on the screenshot you shared: For the first question you have 4 statements. You will leave the number "4" and just edit the ranges to fit into C3:C6. For your second question, you will have to change the values "4" to 3, as it only has 3 statements and then edit as well the ranges to fit into C7:C9.

I am aware this could be a work if your entire questionnaire is too big, but at least it works well.

I hope this solution fits into your problem!