To make decisions easily, we use a weighted scoring model. We can create a weighted scoring model in Excel following some simple steps. In this article, we will show 4 suitable examples. These examples will make you understand the concept of the weighted scoring model. And you will be able to create your model following the examples. So, without further ado, letâ€™s start the discussion.

**Watch Video â€“ Create a Weighted Scoring Model in Excel **

**Table of Contents**hide

## Introduction to Weighted Scoring Model

A weighted scoring model is a process for choosing the best option based on multiple factors or criteria. Weighted scoring models are used to make the decision-making process easy. To complete different projects successfully, we need to make the right decisions. This decision-making process becomes when there are multiple criteria. In those cases, we use a weighted scoring model.

There are two types of scoring models. One is unweighted and another one is the weighted scoring model. In the unweighted scoring model, the weights of different criteria are the same. But in the weighted scoring model, the weights of different criteria are different.

## Steps to Create a Weighted Scoring Model in Excel

To build a successful scoring model, we need to follow some basic steps. The steps are given below.

**STEPS:**

- First of all, specify the most important criteria related to the process.
- Secondly, assign a weight to each criterion. The summation of the weights should be
**100%.** - Thirdly, assign scores to the options.
- Lastly, you need to find the weighted scores. To do so, multiply the weight for each criterion by its score and add them up.

You can easily create a weighted scoring model in Excel by following the above steps. In the following section, we will explain the steps with 4 different examples.

## How to Create a Weighted Scoring Model in Excel: 4 Examples

### 1. Choose the Best Location by Creating a Weighted Scoring Model in Excel

In the first example, we will choose the best location for setting up a production house by creating a weighted scoring model in Excel. For that purpose, we have assigned the criteria and their weights in the dataset below. We have included the numerical scores between **1 **to **5 **for locations **A**, **B **& **C**. Here, we will calculate the weighted average score. For example, the most important thing to set up a production house is the rent. So, the weight of the rent is also the highest. **Location B **has the best score for this criterion. It means the rent of **Location B** is the lowest.

Letâ€™s follow the steps to compute the weighted score.

**STEPS:**

- In the first place, select
**Cell D11**and type the formula:

`=SUMPRODUCT(D6:D10,$C$6:$C$10)/SUM($C$6:$C$10)`

- Hit
**Enter**to see the result.

Here, we have used **the SUMPRODUCT function** to find the sum of the products and divided it by **the SUM function** to calculate the average.

- After that, drag the
**Fill Handle**to the right to see results for**Locations B**&**C**.

- In the end, we can say that
**Location A**is the best option for setting up a production house as it scores the highest.

**Note: **To determine the weighted score without average, use the formula below:

`=SUMPRODUCT(D6:D10,$C$6:$C$10)`

ðŸ”Ž **How Does the Formula Work?**

**SUMPRODUCT(D6:D10,$C$6:$C$10)**

This part of the formula calculates the product for each criterion and then adds them up. Generally, it represents the basic formula,

**D6*C6+D7*C7+D8*C8+D9*C9+D10*C10**

**SUM($C$6:$C$10)**

Here, this part computes the sum of the range **C6:C10**.

**Read More: **How to Calculate Average Score in Excel

### 2. Design a Weighted Scoring Model to Rank the Employees

In this example, we will design a weighted scoring model to rank the employees of a company. To explain the process, we will use the dataset below. To rank the employees, we have assigned **4 **criteria. The weights of these criteria sum up to **10**. The scores of the employees are also distributed.

Letâ€™s pay attention to the steps below to learn the technique.

**STEPS:**

- Firstly, we will calculate the total score out of
**40**. To do so, we will use the**SUM**function. - Select
**Cell D11**and type the formula:

`=SUM(D6:D9)`

- Press
**Enter**to see the result.

- Now, drag the
**Fill Handle**to the right.

- In the second step, we need to compute the weighted score.
- For that purpose, select
**Cell D12**and type the formula:

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

- Hit
**Enter**to see the result.

- Again, use the
**Fill Handle**in**Row 12**for the rest of the cells.

- In the following step, we will calculate the percentage of best. Here, we will use
**the MAX function**. - Select
**Cell D13**and type the formula:

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

- After that, press
**Enter**to see the result.

- One more time, drag the
**Fill Handle**to the right.

- In the last calculation, we will rank the employees. To do so, we will use
**the RANK function**. - Select
**Cell D14**and type the formula:

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

- Then, hit
**Enter**.

- Finally, use the
**Fill Handle**to rank the employees. From the results, we can see**Tom**ranks first.

**Read More: **How to Calculate Total Score in Excel

### 3. Generate a Weighted Scoring Model in Excel and Determine the Highest Priority

Sometimes, we face different requirements to make a decision. Here, we will have **3 **requirements and find which requirement should get the highest priority. The dataset is quite similar to **Method-1**. Like the previous datasets, it also contains some criteria, weights, and scores.

Letâ€™s observe the steps below to find the weighted average score.

**STEPS:**

- In the beginning, select
**Cell D11**and type the formula:

`=SUMPRODUCT(D6:D10,$C$6:$C$10)/SUM($C$6:$C$10)`

- After that, press
**Enter**to see the result.

- Next, drag the
**Fill Handle**to the right to copy the formula.

- In the end, you will get the results. And from the results, we can say
**Requirement Z**should get the highest priority.

### 4. Find Weighted Average by Creating a Scoring Model in Excel

In the last example, we will find the weighted average marks of a student. For example, a student has attended some quizzes, exams, and assignments. At the end of the year, we need to calculate his weighted average marks. For that purpose, we will use the dataset below. We can see the weights of the exams and the scores in percentage. The quizzes, exams, assignments, and attendance are the criteria here.

Letâ€™s follow the steps below to complete the calculation in Excel.

**STEPS:**

- Firstly, select
**Cell B13**. - After that, type the formula there:

`=SUMPRODUCT(D5:D10,C5:C10)/SUM(C5:C10)`

- Finally, press
**Enter**to see the weighted average marks.

**Note: **We didnâ€™t use any absolute reference in this formula, because we applied it to a single cell.

## Things to Remember

Though the process of creating a weighted scoring model is easy, still we need to remember some basic things.

- Donâ€™t forget to use absolute references. Because we will copy the formula in other cells. If you donâ€™t use absolute references, then, you will get incorrect results or errors.
- Assign the criteria carefully.
- The weights can be assigned in numerical values or percentages.

Download the practice book here.

## Conclusion

In this article, we have demonstrated 4 easy examples to Create a Weighted Scoring Model in Excel. Here, we have discussed 4 suitable examples to explain the process. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.