Calculate a grade based on inputs that are pulling from a table

Hello,

First time posting on here but I have a feeling it will not be my last. I am trying to create a an editable gradsheet tracker that can be easily updated. My workbook will have 2 sheet, the master data table and the Data tracker. The master data table will have a score column on the left side running from 0 to 15 (Column A). Along the top will be the criteria that I want (Row 1) In the table will be the potential answers to the criteria. For example:

Master Data Table example.PNG

On the Data tracker I want to calculate a score based on the responses. for example:
Tracker Example.PNG

In the example the formula saw that a yes answer for the hello question is worth 1 point, the 2 minutes answer was worth 2 points and the smile answer of no was 0 points giving us a 3 out of 6 (a perfect score).

My question is how do I create a formula that will look at the cells to the right (did the person say hello?, How long until they introduced themselves?, Did the greeter smile?) see the value of each answer and calculate the score?



Additional information: the Master data table is built the way it is so that people are able to change it and update it and the formulas should still work they are only change how many points each item may be worth.

Welcome to the forum!

You will need a set of INDEX MATCH statements for this. For instance, this would return the score for the 'hello':

=INDEX(score_range,MATCH(hello_cell,hello_range,0))

Attach the actual workbook if you need further help.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I'll have a look once it's attached.

I can't see how you can make the formula any shorter if you want to do it all in one. If it were me. I would use helper columns to return the individual scores and then just work out the percentage in column B. Helper columns can be visible (perhaps next to each of the criteria columns) or invisible (in hidden columns to the right of the data tracker).

Most of the Criteria "Yes" score 1 except Criteria 31

Please try at Data Tracker B4

Formula:

Please Login or Register  to view this content.