### 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.

**Read More: **How to Do Portfolio Optimization Using Excel Solver

### Step 4 – Determine the Final Sum

- Enter the following formula in
**I14**.

`=SUM(H14:H16)`

This is the output.

**Read More: **How to Use Excel Solver for Linear Programming

### Step 5 – Use the Excel Solver

- Create a dataset to see the team rating.

- Click
**File**.

- Select
**More…>Options**.

- Choose
**Add-ins > Solver Add-ins > OK**.

- 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.

**Read More: **Example with Excel Solver to Minimize Cost

## 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.

**Download Practice Workbook**

Download the practice workbook.

## Related Articles

- How to Use Excel Solver to Determine Which Projects Should Be Undertaken?
- Solving Sequencing Problems Using Excel Solver Solution
- Solving Transportation or Distribution Problems Using Excel Solver
- How to Assign Work Using Evolutionary Solver in Excel
- Resource Allocation in Excel
- Solving Equations in Excel

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

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

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”

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.

Thanks a lot for your comment, we will take your suggestions into consideration in the future.