Shajratul Alam Towhid
If you are looking for how to create a scoring matrix in Excel, then you are in the right place. Excel is a platform where we need to deal with diverse types of data and hence do the performance evaluation of employees of a company or students or others. We also do analyses according to our database in Excel. Most of the time we do these things by creating a scoring matrix. In this article, we’ll try to discuss how to create a scoring level in Excel.
Download Practice Workbook
What Is a Scoring Matrix and Where Do We Use It?
In mathematics, a matrix is a collection of data which is kept in a rectangular format. The horizontal part is known as Rows and the vertical part is known as Columns. It is possible to compress and expand the rows and columns of data that make up a matrix. When the matrix incorporates a hierarchy, one useful feature is the ability to drill up and down. A matrix is therefore particularly useful for displaying data.
We can use a scoring matrix as a tool to give various jobs, projects, or properties a relative worth. Using scoring models, governance teams can rate projects according to factors including cost, risk, and potential financial returns. The evaluation of students in an institution or the performance demonstration of the employees of a company can be done by using a scoring matrix.
5 Steps to Create a Scoring Matrix in Excel
We can follow some useful steps to create a scoring matrix in Excel.
Step 01: Making Criteria First to Create Scoring Matrix
Making Criteria is the basic prime term that we need to create a scoring matrix.
- So, firstly, we need to make criteria. Here, we have a column header as Criteria which has 4 The criteria are Accuracy, Discipline, Perseverance, and Analytical Capability which have weights of 1.50, 1.00, 0.50, and 2.00 respectively. These are the individual weights of the criteria within the total weight of 10.
- Secondly, we have put three students’ scores in the dataset. Columns D, E, and F represent the score of Robert, Nicolas, and Henry The range of the score of each student for different criteria is 1-5. The dataset showing Criteria and Score is like this.
Read More: How to Create a Scoring System in Excel (With Easy Steps)
Step 02: Calculating Total Score to Create Scoring Matrix
After making the criteria the second step is to calculate the total score of the individual students. In Row 11, we’ll calculate the total score of students. To calculate the total score of Robert, first, write the following formula in the D11 cell.
=SUM(D6:D9)Here, D6, D7, D8, and D9 are Scores of Robert in Accuracy, Discipline, Perseverance, and Analytical Capability respectively.
- Secondly, press ENTER to find the output as 14.
- Thirdly, use Fill Handle by dragging the cursor rightwards while holding the right-bottom corner of the reference D11
Eventually, we’ll get the scores as output like this.
Read More: How to Calculate Quality Score in Excel (with Easy Steps)
Step 03: Finding Weighted Score to Create Scoring Matrix
We need to find a weighted score after calculating the total score of the students. We can find it by using the SUMPRODUCT function. To find the weighted score of Robert, write the following formula in the D12 cell.
=SUMPRODUCT(D6:D9,$C$6:$C$9)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
- Secondly, press ENTER and use Fill Handle to get all the outputs like this.
Read More: How to Calculate NPS Score in Excel (2 Methods)
Step 04: Calculating Percentage of the Best
In this step our aim is to calculate the percentage of the best. It is done by finding the maximum value among the Weighted Scores first. We can find the maximum value by using the MAX function.
To show this, firstly, write the formula in the D13 cell to calculate the percentage of the best of Robert like this.
=D12/MAX($D$12:$F$12)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
- Secondly, press ENTER and use Fill Handle to find the other outputs.
Read More: How to Create Scorecard in Excel (with Detailed Steps)
Step 05: Determining Rank
Determining rank for each student is the last step. We’ll try to give the students a rank who is better than the others. Using the RANK function, we can do it easily.
Initially, write the formula to find the rank of Robert in the D14 cell like this.
=RANK(D13,$D$13:$F$13)Here, the RANK function mainly finds out the rank of D13 i.e. 1.0000 among the other values of cells D13:F13 i.e. 0.7949 and 0.9231 respectively.
- Similarly, press ENTER and use Fill Handle to find the other outputs like this.
Conclusion
We can create a scoring matrix very easily if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further query.
