Forum Discussion
Calculate score on one column based on percentage of answers in another column
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.
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!
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.
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!