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