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.

Making Criteria First to Create Scoring Matrix


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.

Calculating Total Score to Create Scoring Matrix

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

Calculating Total Score to Create Scoring Matrix


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)
  • Output19.5

Finding Weighted Score to Create Scoring Matrix

  • 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.
  • Output5
  • D12/MAX($D$12:$F$12) → returns the output of division between 5 and 19.5.
  • Output1.0000

Calculating Percentage of the Best

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

Determining Rank

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo