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.
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.
- Second, you will get the desired result after pressing the Enter option.
- Third, insert the following formula in cell F14.
- 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.
- 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.
- Furthermore, you will get the result for the cell and then use the Fill Handle option.
- Finally, you will get the desired result.
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.
- 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.
- Next, go to the File option.
- Then, go to More…>Options.
- Next, go to Add-ins > Solver Add-ins > OK options.
- After that, in the Developer tab select the Excel Add-ins option.
- Afterward, tick the Solver Add-in option and press OK.
- 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.
- 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.
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.