Step 1 – Input Data in Excel
In this example, we’ll show the evaluation process of 3 employees in a certain company.
- You’ll see 4 criteria for assessment.
- The weights of the criteria are different from each other.
- See the below dataset.
Step 2 – Calculate the Totals
- Select cell D11.
- Insert the formula:
=SUM(D6:D9)
- Press Enter.
- Apply AutoFill to the right.
Step 3 – Compute the Weighted Score
- Click on cell D12.
- Insert the formula:
=SUMPRODUCT(D6:D9,$C$6:$C$9)
- Press Enter.
- Use the AutoFill tool to return other weighted scores.
Step 4 – Determine Percentages
- Select cell D13.
- Insert the following formula:
=D12/MAX($D$12:$F$12)
- Press Enter.
- Use the AutoFill tool to the right.
Step 5 – Create a Scoring System Using a Formula
- Select cell D14.
- Insert the formula:
=RANK(D13,$D$13:$F$13)
- Hit Enter.
- It’ll return the rank of the D13 cell value.
- Apply the AutoFill tool to get the ranks of other employees.
Final Output
- Here are the results.
Download the Practice Workbook
<< Go Back to Scoring | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!