How to Create a Scoring Matrix in Excel (with Easy Steps)

What is a Scoring Matrix and Where Do We Use It?

A scoring matrix is a valuable tool for assessing the relative worth of various items, such as jobs, projects, or properties. It allows governance teams to evaluate factors like cost, risk, and potential financial returns. Additionally, scoring matrices are useful for assessing student performance or employee demonstrations.

Step 1: Define Criteria for the Scoring Matrix

• Start by creating a column header labeled Criteria.
• Define four criteria: Accuracy, Discipline, Perseverance, and Analytical Capability.
• Assign weights to each criterion (e.g., 1.50, 1.00, 0.50, and 2.00, respectively) within a total weight of 10.
• Enter student scores (e.g., Robert, Nicolas, and Henry) for each criterion (scores range from 1 to 5).

The dataset showing Criteria and Score is like this.

Step 2: Calculate Total Scores

• Calculate the total score for each student.
• For Robert, use the formula in cell D11:
`=SUM(D6:D9)`
• This sums up Robertâ€™s scores for Accuracy, Discipline, Perseverance, and Analytical Capability.

• Press ENTER to find the output as 14.
• Drag the fill handle to compute scores for other students.

Step 3: Compute Weighted Scores

• Use the SUMPRODUCT function to find the weighted score for each student.
• For Robert, use the formula in cell D12:
`=SUMPRODUCT(D6:D9,\$C\$6:\$C\$9)`
• This calculates the weighted sum of scores based on the assigned weights.

Formula Explanation:

• SUMPRODUCT(D6:D9,\$C\$6:\$C\$9) â†’ returns the sum of D6*C6, D7*C7, D8*C8, D9*C9, i.e. (1.50*3 + 1.00*4 + 0.50*2 + 2.00*5)
• Output â†’ 19.5

• Press ENTER and drag the Fill Handle to compute weighted scores for all students.

Step 4: Determine Percentage of the Best

• Calculate the percentage of the best score for each student.
• For Robert, use the formula in cell D13:
`=D12/MAX(\$D\$12:\$F\$12)`
• This divides Robertâ€™s weighted score by the maximum weighted score.

Formula Explanation:

• MAX(\$D\$12:\$F\$12) â†’ returns the maximum value among cells D12, E12, F12 E. among 19.5, 15.5, 18.
• Output â†’ 5
• D12/MAX(\$D\$12:\$F\$12) â†’ returns the output of division between 5 and 19.5.
• Output â†’ 1.0000

• Press ENTER and drag the Fill Handle to compute percentages for other students.

Step 5: Assign Ranks

• Determine the rank for each student using the RANK function.
• For Robert, use the formula in cell D14:
`=RANK(D13,\$D\$13:\$F\$13)`
• This ranks Robertâ€™s percentage among other students.

• Press ENTER and drag the Fill Handle to assign ranks to all students.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Scoring | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF