Aung Shine
Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store important data. Sometimes, they need to perform assessments of the students or employees and it’s easier to do that in excel. Usually, after the evaluation, they’ll be given ranks or scores. This article will show you the step-by-step procedures to Create a Scoring System in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures to Create a Scoring System in Excel
In this article, we’ll make use of some built-in excel functions for creating the scoring system. We’ll demonstrate the whole process with easy steps. Therefore, go through the below steps carefully to perform the task.
STEP 1: Input Data in Excel
- Firstly, we’ll input the precise data.
- In this example, we’ll show the evaluation process of 3 employees in a certain company.
- Here, you’ll see 4 criteria for assessment.
- Again, the weights of the criteria are different from each other.
- See the below dataset to understand it clearly.
STEP 2: Calculate Total Number
- Now, we’ll calculate the total number using the SUM function obtained by each employee in the assessment.
- For this purpose, select cell D11.
- Next, type the formula:
=SUM(D6:D9)- Subsequently, press Enter.
- Thus, you’ll get the total number.
- After that, apply the AutoFill tool on the right side.
- Hence, it’ll return the total number of other employees.
Read More: How to Calculate Total Score in Excel (4 Easy Ways)
STEP 3: Compute Weighted Score
However, the total number obtained by the employees may not be the final score. Because the weight of each criterion is different. And it plays a major role in the total evaluation. So, we need to find out the weighted score of each employee. In this regard, we’ll insert the SUMPRODUCT function. This function calculates the sum of some product outputs. Therefore, learn the following process to carry out the operation.
- First of all, click cell D12.
- Here, insert the formula:
=SUMPRODUCT(D6:D9,$C$6:$C$9)- Then, press Enter.
- Afterward, use the AutoFill tool to return other weighted scores.
- In this way, you’ll get the desired outputs.
Similar Readings
- How to Calculate Average Score in Excel (7 Suitable Ways)
- How to Calculate Quality Score in Excel (with Easy Steps)
STEP 4: Determine Percentage
Moreover, we’ll determine the percentage of each weighted score compared to the best one. In this step, we’ll use the MAX function. This function retrieves the maximum number from a specified range of numbers. Hence, follow the process below.
- In the beginning, select cell D13.
- There, input the formula:
=D12/MAX($D$12:$F$12)- Return the rest by pressing Enter.
- Consequently, use the AutoFill tool on the right side.
- As a result, you’ll get the outcomes.
STEP 5: Create Scoring System Using Formula
Finally, we’ll create the scoring system using a formula. We’ll apply the RANK function. This function gives out the rank of a number. In the argument, we have to input the range of numbers first. So, see the process below.
- Select cell D14 at first.
- There, insert the formula:
=RANK(D13,$D$13:$F$13)- Next, hit Enter.
- Thus, it’ll return the rank of the D13 cell value.
- Lastly, apply the AutoFill tool to get the ranks of other employees.
- In this way, you can easily create a scoring system in excel.
Read More: How to Create Scorecard in Excel (with Detailed Steps)
Final Output
At last, the Scoring System is ready to demonstrate. Look at the following picture which is our final output in Excel.
Conclusion
Henceforth, you will be able to Create a Scoring System in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
