# How to Use the Excel Solver to Rate Sports Teams -5 Steps

### Step 1 – Create a Dataset

This sample dataset showcases Home in B4, Mean in B6, Team in E4, Rating in F4. Home Team, Home Score, Visitor Team, and Visitor Score are displayed in columns B, C, D, and E.

### Step 2 -Determine Predicted Points

• Enter the following formula in C6.
`=AVERAGE(\$F\$5:\$F\$7)`

• Press Enter.

• Enter the following formula in F14.
`=\$C14-\$E14`

• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

• Enter the following formula in G14.
`=\$C\$4+VLOOKUP(B14,\$E\$5:\$F\$11,2,FALSE)-VLOOKUP(D14,\$E\$5:\$F\$11,2,FALSE)`

• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Step 3 – Calculate the Square Error

• Enter the following formula in H14.
`=(F14-G14)^2`

• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

### Step 4 – Determine the Final Sum

• Enter the following formula in I14.
`=SUM(H14:H16)`

This is the output.

### Step 5 – Use the Excel Solver

• Create a dataset to see the team rating.

• Click File.

• Select More…>Options.

• In the Developer tab, select Excel Add-ins.

• Check Solver Add-in and click OK.

• Go to Data>Solver to open the Solver Parameters dialog box.

• Enter the target cell in Set Objective, the constraints cell in Subject to the Constraints, and the cells to change in By Changing Variable Cells section.
• Click Solve.
• Click OK.

This is the output.

## Things to Remember

• As the objective is to rate each team. Home-field edge and Team ratings are entered in By Changing Cells.
• To make the prediction as close as possible to the outcome of each game, the sum differences between the Actual outcome and the Predicted outcome are entered in the target cell.
• Positive and negative prediction errors can cancel each other out. If you over-predict the home team margin by 5 points in one game and under-predict it by 5 points in another game, the sum of the two differences will yield a value of 0 when, in fact, you were off by 10 points for a game. To avoid this, add 2 powers of [(Actual Outcome) – (Predicted Outcome)] to all games.
• This model uses GRG Nonlinear in the last step.

## Related Articles

<< Go Back to Excel Solver Examples | Solver in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet... Read Full Bio

1. Just found this article on GR i am not sure in your explanation when you said seattle has 4 point better than st Louis. Can you please explsin this?
Thank you

Rubayed Razib Suprov Aug 30, 2022 at 10:22 AM

Thanks for your question. Here Seattle has a rating of -0.50409…And St Louis Rams has -4.439784. if you subtract -0.50409 from the -4.439784, then you will get 4.943874.which is approximately around 4. So the statement that “Seattle has 4 points better than St Louis”

2. I think is much better to obtain the average points per game.
Home team vs Opponent team and then to obtain (MOV) Margin of Victory. MOV +/-
Thus this will give you a better point of view which team will win the match.
And or Apply forecast or trend functions at least with the last three seasons.
I know all we have a different way or sistem to predict game scores.

Advanced Excel Exercises with Solutions PDF