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