Creating a weighted scoring system using data entered on a form

kbergstrom

I am creating a scoring system to review customer service contacts that I want to be able add weighted scoring to.

I have five key categories that I want to account for 20% of the total each, and within those categories, 2-4 subcategories where a rating of "Achieved" "Partially Achieved" or "Not Achieved" will be assigned. I would like to assign a score of 100% to anything marked as Achieved, 50% to Partially Achieved, and 0% to Not Achieved.

Has anyone been able to use a formula or have a specific function in mind that will help me automatically calculate a final score including the subcategories and weight for the five key categories? I currently have a form designed to enter in the data with conditional logic for the questions asked depending on the type of service provided that I really love, but am stumped on where/what formula will help complete the scoring calculations needed.

Paul Newcome

Are all of these columns next to each other? If so, you can use something along the lines of:

=(COUNTIFS([1st Scoring Column]@row:[Last Scoring Column]@row, @cell = "Achieved") + (COUNTIFS([1st Scoring Column]@row:[Last Scoring Column]@row, @cell = "Partially Achieved") * 0.5)) / COUNTIFS([1st Scoring Column]@row:[Last Scoring Column]@row, OR(@cell = "", @cell <> ""))