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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

`=SUM(D6:D9)`

`=SUMPRODUCT(D6:D9,\$C\$6:\$C\$9)`

`=D12/MAX(\$D\$12:\$F\$12)`

`=RANK(D13,\$D\$13:\$F\$13)`

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

`=COUNTIF(C5:C15,"Yes")*C19+COUNTIF(D5:D15,"Yes")*C20+COUNTIF(E5:E15,"Yes")*C21+COUNTIF(F5:F15,"Yes")*C22+COUNTIF(G5:G15,"Yes")*C23`

## How to Calculate Grade Scores in Excel

### 1. Applying IF Function

`=IF(G5>90%,\$C\$13,G5>80%,\$C\$14,G5>70%,\$C\$15,G5>40%,\$C\$16,TRUE,\$C\$17)`

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.

`=IFS(G5>90%,\$C\$13,G5>80%,\$C\$14,G5>70%,\$C\$15,G5>40%,\$C\$16,TRUE,\$C\$17)`

### 3. Utilizing VLOOKUP Function

`=VLOOKUP(G5,\$B\$13:\$C\$17,2,TRUE)`

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

`=AVERAGE(D5:D11)`

`=STDEV.P(D5:D11)`

• Use this formula to find the Z score.

`=(D5-\$D\$13)/\$D\$14`

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!

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

`=RANK(C5,\$C\$5:\$C\$15)+SUMPRODUCT(--(\$C\$5:\$C\$15=\$C5),--(D5<\$D\$5:\$D\$15))`

• 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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF