A weighted rank represents the rank of some observation in a dataset that has been weighted by various factors.
For example, suppose you would like to rank basketball players from best to worst and you have several factors to consider for each player including:
- Points
- Assists
- Rebounds
The following example shows how to calculate weighted ranks in Excel for this exact scenario.
Example: How to Calculate Weighted Ranking in Excel
First, we will create the following dataset that summarizes the points, assists and rebounds for various basketball players along with the weights that each of these factors should be given when ranking the players:
Note: The value for the weights must add up to 1.
Next, we will calculate a weighted average for player using the weights for each factor and the individual values for each player.
We will type the following formula into cell E3:
=SUMPRODUCT(B3:F3, $B$2:$F$2)
We can then click and drag this formula down to the remaining cells in column E:
From the results we can see:
- Andy has a weighted average of 19.2.
- Bob has a weighted average of 14.8.
- Chad has a weighted average of 23.8.
- Doug has a weighted average of 26.
- Eric has a weighted average of 19.4.
Next, we can type the following formula into cell F3 to assign a weighted ranking to each player:
=RANK(E3, $E$3:$E$7)
We can then click and drag this formula down to the remaining cells in column F:
The player with the highest weighted average is given a rank of 1 while the player with the lowest weighted average is given a rank of 5.
We can see that Doug has a rank of 1, so we would consider him to be the “best” player.
If you would instead like to assign a rank of 1 to the player with the lowest weighted average, you can use the following formula instead:
=RANK(E3, $E$3:$E$7, 1)
The following screenshot shows how to use this formula in practice:
Now the player with the highest weighted average is given a rank of 5 while the player with the lowest weighted average is given a rank of 1.
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
