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