A weighted scoring model is a type of model you can use to choose the best option among several options based on factors that are weighted with certain values.
For example, suppose you would like to choose the best location to live among three possible choices and you have several factors to consider for each location including:
- Affordability
- Weather
- Career options
- Crime
- Food quality
The following example shows how to create a weighted scoring model in Excel for this exact scenario.
Example: How to Create a Weighted Scoring Model in Excel
First, we will create the following dataset that assigns a value of 1 to 10 for each of the five factors we’re considering for each location:
Note: The value for the weights must add up to 1.
Next, we will calculate a weighted average for each location using the weights for each factor and the individual values for each location.
We will type the following formula into cell G3:
=SUMPRODUCT(B3:F3, $B$2:$F$2)
We can then click and drag this formula down to the remaining cells in column G:
From the results we can see:
- Location 1 has a weighted average of 6.6.
- Location 2 has a weighted average of 6.2.
- Location 3 has a weighted average of 5.95.
Based on these calculations, we would say that location 1 is the best overall location based on the specific weights that we have used for each factor.
How the Formula Works
Recall the formula that we used to calculate a weighted average for each location:
=SUMPRODUCT(B3:F3, $B$2:$F$2)
This formula works by multiplying each weight by each individual value for each location, then taking the sum of all of these values.
For example, here is how the formula calculated the weighted average for Location 1:
Weighted Avg: (0.4*8) + (0.2*4) + (0.15*6) + (0.15*8) + (0.1*5) = 6.6
The weighted average for each location was calculated in a similar manner.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Weighted Percentage in Excel
How to Find Weighted Moving Averages in Excel
How to Calculate Weighted Standard Deviation in Excel
