# How to Create a Scoring Formula in Excel (System, Text, Grade, Z Score)

Scoring formulas in Excel are valuable for ranking and evaluating datasets. They allow us to calculate scores efficiently, consider weights, rewards, and penalties. Let’s explore how to create scoring systems using various functions.

## Scoring System Requirements

To build an Excel scoring system, you’ll need four key formulas:

• Total Score (SUM Function):

`=SUM(D6:D9)`

• Weighted Average Score (SUMPRODUCT Function):

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

• Percentile Calculation (MAX Function):

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

• Employee Ranking (RANK Function):

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

## Text-Based Scoring Formula

For text-based answers, use the COUNTIF function to create the following formula (applied 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`

### 1. Using the 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 the IFS Function

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

### 3. VLOOKUP for Grade Scores

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

## Z Score Calculation

When comparing values to the average and variability of a group, the Z score enhances accuracy.  Follow these steps:

• Calculate the average (cell D13):

`=AVERAGE(D5:D11)`

• Compute the standard deviation (cell D14):

`=STDEV.P(D5:D11)`

• Find the Z score using this formula:

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

Read More: How to Calculate T Score in Excel

## Things to Remember

• For greater than or less than comparisons, include equal signs (i.e., <= or >=).
• Named ranges can simplify VLOOKUP formulas.
• Be cautious when working with nested IF formulas; close all parentheses properly.

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

To rank based on multiple criteria in Excel, you can utilize the RANK function in conjunction with either the SUMPRODUCT or COUNTIF function. Here’s an example:

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

The RANK function calculates the rank number from the range \$C\$5:\$C\$15 based on the value in cell C5. This accounts for duplicate values in cells C10 and C11 (resulting in a rank number of 2). The SUMPRODUCT function handles situations where there are tied values, returning 0 when there are none and 1 when there is a tie. Notably, the double negative operator () is used to convert TRUE/FALSE results into 1/0, respectively. This formula effectively avoids duplicate rank numbers.

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

Conditional formatting can be applied through the Home tab. Select your data and navigate to the Conditional Formatting option in the Styles group. From there, you’ll find various options to apply conditional formatting. For instance, you can highlight the top ten percent values by selecting Top 10% from the Top/Bottom Rules.

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

To incorporate bonuses or penalties into a scoring formula, first, outline the criteria for adding or deducting points. Then, use the IF function to integrate these criteria into the main scoring system. You may need to create an additional column to accommodate this adjusted scoring system.

## Excel Scoring Formula: Knowledge Hub

<< Go Back to Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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