Search code examples
excelsolver

How do I use Excel Solver with IF statements?

I have a table of tennis players where I've worked out the probability of them winning their respective matches in three different ways, e.g. % previous 10 games won, % first serves in play etc etc. I've assigned a weighting to each of these probabilities to create a blended probability which I convert into predicted odds. I then compare these predicted odds to the actual odds that are available from bookmakers to derive the 'edge'. A positive edge means the bookmaker is offering better odds than they should and there is 'value' in placing a bet. There are a three potential scenarios:

  1. Edge >= Edge threshold, player wins then I win £100 x actual odds
  2. Edge >= Edge threshold, player loses then I lose £100
  3. Edge < Edge threshold, I don't bet so neither win nor lose

I want to work out what the optimal combination of the three probabilities AND the edge threshold that generates the highest level of profit. I tried to use Excel Solver to do this but apparently it can't cope with IF statements. I've read in a few places that you can convert IF statements into complex formulas that get around this problem but I haven't been able find an example that's clear enough to follow. What are the potential solutions?

For reference here is a sample of the table:


+-----+---------------+---------------+---------------+---------------------+----------------+--------------+--------+-----------+-------------+
| PKG | Probability 1 | Probability 2 | Probability 3 | Blended probability | Predicted odds | Actuals odds |  Edge  | WIN/ LOSE | Overall P&L |
+-----+---------------+---------------+---------------+---------------------+----------------+--------------+--------+-----------+-------------+
|   1 | 32.4%         | 28.6%         | 74.0%         | 43.4%               |           2.31 |         2.81 |   7.86 |         1 |           0 |
|   2 | 36.2%         | 34.1%         | 61.0%         | 42.8%               |           2.34 |         4.47 |  20.39 |         0 |        -100 |
|   3 | 55.2%         | 58.2%         | 42.0%         | 52.4%               |           1.91 |         4.13 |  28.19 |         1 |         413 |
|   4 | 60.7%         | 64.7%         | 67.0%         | 64.2%               |           1.56 |         1.44 |  -5.17 |         0 |           0 |
|   5 | 33.7%         | 40.1%         | 75.0%         | 48.6%               |           2.06 |         2.32 |   5.57 |         1 |           0 |
|   6 | 48.7%         | 47.8%         | 77.0%         | 56.8%               |           1.76 |         3.41 |  27.46 |         0 |        -100 |
|   7 | 55.2%         | 64.1%         | 79.0%         | 65.9%               |           1.52 |         2.38 |  23.87 |         1 |         238 |
|   8 | 45.6%         | 34.9%         | 30.0%         | 36.6%               |           2.73 |         3.70 |   9.63 |         0 |           0 |
|   9 | 56.9%         | 63.1%         | 49.0%         | 57.0%               |           1.75 |         3.00 |  23.64 |         1 |         300 |
|  10 | 38.6%         | 37.6%         | 25.0%         | 34.1%               |           2.93 |         3.25 |   3.32 |         0 |           0 |
|  11 | 56.4%         | 56.4%         | 63.0%         | 58.4%               |           1.71 |         2.74 |  21.90 |         1 |         274 |
|  12 | 71.3%         | 78.1%         | 38.0%         | 64.1%               |           1.56 |         3.66 |  36.75 |         0 |        -100 |
|  13 | 61.4%         | 67.5%         | 72.0%         | 67.0%               |           1.49 |         1.23 | -14.50 |         1 |           0 |
|  14 | 66.4%         | 73.0%         | 76.0%         | 71.9%               |           1.39 |         1.42 |   1.46 |         0 |           0 |
|  15 | 56.1%         | 64.8%         | 21.0%         | 49.1%               |           2.04 |         1.35 | -25.19 |         1 |           0 |
|  16 | 61.5%         | 53.4%         | 57.0%         | 56.9%               |           1.76 |         1.37 |  21.03 |         0 |        -100 |
|  17 | 32.4%         | 26.2%         | 45.0%         | 33.7%               |           2.97 |         1.45 | -35.14 |         1 |           0 |
|  18 | 69.5%         | 62.5%         | 43.0%         | 58.8%               |           1.70 |         1.77 |   2.19 |         0 |           0 |
|  19 | 65.0%         | 65.3%         | 45.0%         | 59.1%               |           1.69 |         3.66 |  31.81 |         1 |         366 |
|  20 | 73.1%         | 81.9%         | 45.0%         | 68.2%               |           1.47 |         1.12 | -21.03 |         0 |           0 |
|  21 | 52.2%         | 51.4%         | 25.0%         | 43.7%               |           2.29 |         2.59 |   5.14 |         1 |           0 |
|  22 | 30.9%         | 38.2%         | 27.0%         | 32.6%               |           3.06 |         2.26 | -11.61 |         0 |           0 |
|  23 | 61.6%         | 43.1%         | 46.0%         | 49.5%               |           2.02 |         2.82 |  14.11 |         1 |           0 |
|  24 | 58.6%         | 73.4%         | 34.0%         | 57.1%               |           1.75 |         4.21 |  33.38 |         0 |        -100 |
|  25 | 64.4%         | 72.8%         | 61.0%         | 66.7%               |           1.50 |         2.24 |  21.99 |         1 |         224 |
+-----+---------------+---------------+---------------+---------------------+----------------+--------------+--------+-----------+-------------+

Here are the weightings:

+---------------+-----------+
|     Blend     | Weighting |
+---------------+-----------+
| Probability 1 | 30.0%     |
| Probability 2 | 40.0%     |
| Probability 3 | 30.0%     |
+---------------+-----------+

Predicted odds are calculated using the following formula:

=1/Blend prob

The edge is calculated using the following formula:

=(100/Pred odds)-(100/Act odds)

The current edge threshold is 20.

The IF statement to calculate the Overall P&L is:

=IF(WIN/LOSE=1,IF(Edge>=Edge threshold,100*Act odds,0),IF(Edge>=Edge threshold,-100,0))

I set up Solver as follows:

  1. Objective: Max of a cell that summed the Overall P&L cells
  2. Variables: i ) the weightings cells above as fixed values and ii) a cell with the edge threshold as a fixed value
  3. Constants: A cell that sums the weightings - set at 1 (100%) and the Edge threshold as an integer
  4. Method: Tried GRG Nonlinear and Evolutionary (the latter is one that is supposed to be able to handle 'non-smooth' problems, i.e. IF statements)
Solution

  • So I have tested your sample data using Solver with the following set up:

    Attempt2

    The Variables range is C29:C32 (the three weightings and the threshold);

    The constraints are:

    • C29:C31<=1 as they are probabilities;
    • C32<=100 must set an upper bound for this variable so I picked 100 which should be safe?
    • C33=1 which is the sum of the three weightings which should be 100% exact?

    Use Evolutionary method I have obtained the following:

    • Max Profit: 2210;
    • Probability 1: 37.5%
    • Probability 2: 9.1%
    • Probability 3: 53.4%
    • Threshold: 18

    I think the WIN/LOSS result plays a significant role in this exercise but what would be the reasonable constraints for this range except binary?