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

[ClaudiaTay]

Hi all,

I have tried scouring the internet for a solution to my Excel problem but could not find anything that works. 

I am looking for the "Score" column to automatically calculate and come up with a score based on the Y/N/NA column.

Scoring system:

1 - (Y to 50% or less of statements)
2- (Y to more than 50% of statements)
3 - (Y to 100% of statements)

For example, for Q1 if there are 2 Ys and 2 Ns, the score will be 1. If there are 3 Ys and 1 N, the score will be 2. 4 Ys and 0 N, score will be 3. 

There are some issues here that make this more challenging as each question have different number of statements. 

I am not sure if this is possible to do via Excel?

Please help. Thank you!

[Edit]

I am using the Office 365 Microsoft Excel on Windows. Specifically: 

Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit

[#2]

@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!

[#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.

[#4]

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

[#5]

@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.

[#6 · Accepted solution]

@ClaudiaTay 

In your post, I do not see a reason to assume that you are using an older version of Excel.  But you did not specify which version you are using, or on which platform (Windows, Mac, a web browser…) you are using Excel.

See the attached workbook, starting with the _Info worksheet.  Excel 2021 or a later version is required.  My solution does not require knowledge of pivot tables, or excessive formula manipulation.  There are questions regarding how NA responses should be handled.

[#7]

Hi @MAngosto 

I appreciate your help but it does not seem to work for me. I changed the ranges but they still don't work. I only have 20 questions and I am willing to personalise each score but when I paste the formula I get the "There's a problem with this formula" message.

[#8]

Thank you for this but it does not quite work for what I am looking for as this sheet goes out to other users who would probably be confused by the multiple scores for each question.

[#9]

@ClaudiaTay 

Hello!

Have you tried to rearrange the syntax of my formula to your current "language". If you see, I use ";" as a separator, but you are possibly needing it to be ",".

If that does not solve the issue, please let me know with a screenshot of your formula and error message!

[#10]

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.

[#11]

@SnowMan55 

Thank you so much! I had to edit it a little but it worked for me. Appreciate your detailed explanation as well.