Excel Scoring Formula (System, Text, Grade, Z Score)

In this article, you will learn how to create a scoring formula in Excel. This includes the scoring system, scoring based on text, calculating grades with three formulas, and a formula to find the Z score in Excel.

Excel scoring formulas are useful to rank and evaluate a dataset. This allows us to incorporate efficient score calculation, weights, rewards, and penalties.

We can analyze questionnaires using the text-based score calculation system. Formulas make the process of ranking data automatic and help with number-based decision-making.

The Z score also standardizes the data and allows us to make meaningful comparisons.

Download Practice Workbook

You can download the Excel file from the link below.

Function Requirements to Create a Scoring System Formula in Excel

• You need to use four formulas to create an Excel scoring system.
• Use the SUM function to return the total score.

• Apply the SUMPRODUCT function to calculate the weighted average score.

• Utilize the MAX function to find the percentile of the score.

• Finally, apply the RANK function to rank the employee.

Excel Scoring Formula Based on Text Answers

• Apply the COUNTIF function and create the following formula to return the score based on text. We have applied it in cell F17.

How to Calculate Grade Scores in Excel

1. Applying IF Function

• Utilize the IF function to return the students’ grades.

Read More: How to Create a Scoring System in Excel

2. Using IFS Function

• Insert the IFS function and create the following formula to calculate the grades of the students.

3. Utilizing VLOOKUP Function

• Use the VLOOKUP function to find the grade scores.

Excel Scoring Formula: Find Z Score in Excel

When comparing values to the average and variability of an entire group, the Z score can be used in the scoring formula to compare them accurately. This improves the accuracy of the evaluation.

• Use the AVERAGE function in cell D13.

• Insert the STDEV.P function in cell D14.

• Use this formula to find the Z score.

Read More: How to Calculate T Score in Excel

Things to Remember

• If we use numbers instead of percentages, then in the formula, we will need to use the numbers as criteria.
• When we need to use greater than or less than, we will need to attach an equal sign (i.e., <= or >=).
• We can also use the named range as the lookup range in the VLOOKUP formula.
• You need to be careful when working with the nested IF formulas and conclude all the IF formulas using the close parenthesis.

Conclusion

This article covered the Excel scoring formula. If you have any questions or suggestions about this, please feel free to comment below, and the ExcelDemy team will try to solve the issue as soon as possible. Thanks for reading, and keep excelling!

Frequently Asked Questions

1. How to rank values based on multiple criteria in Excel?

You can combine the RANK function with SUMPRODUCT or COUNTIF function to rank based on multiple criteria.  The following formula shows how to do this by combining the RANK and SUMPRODUCT functions.

• The RANK function returns the rank number from the $C$5:$C$15 cell range based on the C5 cell with the duplicate values in the C10 and C11 cells (the rank number is 2).
• And, the SUMPRODUCT function finds 0 in case of no tied values. But it returns 1 for the C10 cell.
• Notably, the (–) operator is used to return 1 instead of getting TRUE and 0 for FALSE.
• Thus, you can easily avoid the duplicate rank number using this formula.

2. How to apply conditional formatting in the scoring formula in Excel?

The conditional formatting feature is available in the Home tab. Select your data and click on the Conditional Formatting option under the Styles group. Then there are various options to apply the conditional formatting. For example, you can highlight the top ten percent values from the Top/Bottom Rules >>> selecting Top 10%.

3. How to incorporate bonus or penalty in the scoring formula in Excel?

You need to list criteria to add points and deduct points. After that, you will need to use the IF function to incorporate those criteria into the main scoring system. You may insert another column for this adjusted scoring system.

Excel Scoring Formula: Knowledge Hub

<< Go Back to Formula List | Learn Excel