How to Use Excel Solver to Rate Sports Team (With Easy Steps)

This tutorial will demonstrate how to use an excel solver to rate sports team. Ratings are numerical representations of competitive strength which is directly comparable. It is often applied directly to predict the game outcome between any two teams. The Sports rating systems have been around for almost 80 years and use a variety of methods for rating teams. Today we would like to discuss how to rate sports teams using Excel Solver.


Download Practice Workbook

You can download the practice workbook from here.


What Is an Excel Solver?

Excel Solver is used in many different algorithms for finding the solution to different complex linear and non-linear problems. The methods of excel solver are.

  • GRG Nonlinear: The full form of this method is the Generalized Reduced Gradient Nonlinear method.  Mainly for simple nonlinear problems where the data will contain a minimum of one constraint, this method is used.
  • LP Simplex: This method is used when a Linear Programming problem by linear relationships occurs. With the linear connection with the data, linear programming tends to achieve the desired goal such as minimum loss, maximum profit, etc.
  • Evolutionary: Among all the types, this is the most difficult where you can not determine the characteristics of the function. The system of this kind of function can increase or decrease at any time. This is because the functions are either discontinuous or nonsmooth.

Step-by-Step Procedures to Use Excel Solver to Rate Sports Team

If you follow the steps correctly, you should learn how to excel solver to rate sports team on your own. The steps are:

Step 1: Arranging Dataset

Our goal is to use the solver to rate sports teams in excel. In this case, we have the Home in cell B4, Mean in cell B6, Team in cell E4, Rating in cell F4, Home Team, Home Score, Visitor Team, and Visitor Score sequentially in column B, column C, column D, and column E . We’ll use this sample dataset overview as an example in Excel to understand easily.

Dataset to to Use Excel Solver to Rate Sports Team


Step 2: Determining Predicted Points

Now, we want to determine the predicted points of the teams. To do so, we have to follow the below steps.

  • First, insert the following formula in cell C6.
=AVERAGE($F$5:$F$7)

Inserting Formula to Use Excel Solver to Rate Sports Team

  • Second, you will get the desired result after pressing the Enter option.

Showing Result to Use Excel Solver to Rate Sports Team

  • Third, insert the following formula in cell F14.
=$C14-$E14

Inserting Formula to Use Excel Solver to Rate Sports Team

  • Fourth, you will get the result for the cell and then use the Fill Handle option.

  • Fifth, you will get the desired result.

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

  • Seventh, you will get the result for the cell and then use the Fill Handle option.

  • Last, you will get the desired result.


Step 3: Calculating Square Error

To have an accurate result, we will also determine the square error. We can calculate square error by the following steps.

  • To begin with, insert the following formula in cell H14.
=(F14-G14)^2

Inserting Formula to Use Excel Solver to Rate Sports Team

  • Furthermore, you will get the result for the cell and then use the Fill Handle option.

  • Finally, you will get the desired result.

Inserting Formula to Use Excel Solver to Rate Sports Team


Step 4: Determination of Final Sum

In this case, we will determine the final sum of the process by following the below process.

  • Firstly, insert the following formula in cell I14.
=SUM(H14:H16)

  • Lastly, you will get the desired result.


Step 5: Use of Excel Solver

If you already have a dataset of Points, Errors, and Sum but don’t have any team rating, then you can determine it using an excel solver. Excel solver will give you a much more accurate value by predicting non-linear calculations. We will use an excel solver to ease our work in the following steps.

  • First, we have arranged a dataset where the team rating is missing.

Dataset Inserting Formula to Use Excel Solver to Rate Sports Team

  • Next, go to the File option.

Adding Excel Solver Use Solver to Rate Sports Team in Excel

  • Then, go to More…>Options.

Adding Excel Solver Use Solver to Rate Sports Team in Excel

  • Next, go to Add-ins > Solver Add-ins > OK options.

Adding Excel Solver Use Solver to Rate Sports Team in Excel

  • After that, in the Developer tab select the Excel Add-ins option.

  • Afterward, tick the Solver Add-in option and press OK.

Adding Excel Solver Use Solver to Rate Sports Team in Excel

  • Furthermore, go to Data>Solver options to open the Solver Parameters dialog box.

  • The Solver Parameters dialog box for this model is shown below image. Select the desired target cell in the Set Objective Section, the constraints cell in the Subject to the Constraints section, and the cells that will be used to change are selected in the By Changing Variable Cells section.
  • After clicking on the Solve and then OK in the Solver Results dialog box, we can get the following results.

Inserting Formula to Use Excel Solver to Rate Sports Team

  • Finally, you will get the desired result with the below changes.


Things to Remember

  • From the problem, we can see that our objective is to give ratings for each team. Home-field edge and Team ratings are By Changing Cells in our problem instead in our process.
  • We want the prediction to be as close as possible to the outcome of each game. It implies that to minimize the sum over all differences between the Actual outcome and the Predicted outcome can be used as the target cell.
  • However, there is a problem with this process. The positive and negative prediction errors can cancel each other out. For example, if you over-predict the home team margin by 5 points in one game and under-predict the home team margin by 5 points in another game, the sum of the two differences will yield a value of 0. But in fact, you were off by 10 points for a game. To not cancel out both positive and negative errors, we can use another method – to add up 2 power of [(Actual Outcome) – (Predicted Outcome)] for all games.
  • Another thing that requires your attention is that this model is no longer a simple LP model. We selected GRG Nonlinear engine in the last step.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to rate sports teams in excel solver. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

Zhiping Yan

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 via programming. This will save me a lot of time. I am keen to learn new things.

4 Comments
  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

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

Leave a reply

ExcelDemy
Logo