I have a sheet keeping track of football results and I want to test it using different staking, rather than just level stakes (ie 1 unit on every bet)
Starting Bank is in J2
So I want to stake to win a % of the bank. That formula is very easy; it is basically (current bank * 0.02)/(odds -1) In my case =(J2*0.02)/(Z5-1). That works fine for the first row, but of course the next row now needs to reflect the profit or loss from the row above, as the bank will have changed. Again, it is not difficult to do. This time it is (cumulative bank * 0.02)/(odds-1). In my case =(Q5*0.02)/(Z6-1)
The difficulty arises with filtered data, as the purpose of this is to test various scenarios to see what gives the best overall profit. Once data is filtered, unless the first row of data, Row 5, is still in place, there is now no row above to reference. When filtered, the now visible first row would again need to reference the starting bank in J2 and not the cumulative bank in column Q in the row above.
Column N is where the staking calculation is done. I thought of having the cells in N reference the cumulative profit, Column P, but again, in a filtered sheet, how can I keep this accurately calculating it based the current bank?
Any help gladly accepted.
Starting Bank is in J2
So I want to stake to win a % of the bank. That formula is very easy; it is basically (current bank * 0.02)/(odds -1) In my case =(J2*0.02)/(Z5-1). That works fine for the first row, but of course the next row now needs to reflect the profit or loss from the row above, as the bank will have changed. Again, it is not difficult to do. This time it is (cumulative bank * 0.02)/(odds-1). In my case =(Q5*0.02)/(Z6-1)
The difficulty arises with filtered data, as the purpose of this is to test various scenarios to see what gives the best overall profit. Once data is filtered, unless the first row of data, Row 5, is still in place, there is now no row above to reference. When filtered, the now visible first row would again need to reference the starting bank in J2 and not the cumulative bank in column Q in the row above.
| ToWin_OO_145_2.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| N | O | P | Q | Z | |||||||||||
| 4 | Stake To Win 2% | P&L @ 2% | Cum. P&L | Cum. Bank @ 2% | Betfair Exchange Odds | ||||||||||
| 5 | $55.56 | $20.00 | $20 | $1,020.00 | 1.36 | ||||||||||
| 6 | $61.82 | $20.40 | $40.40 | $1,040.40 | 1.33 | ||||||||||
| 7 | $160.06 | -$160.06 | -$119.66 | $880.34 | 1.13 | ||||||||||
| 8 | $135.44 | $17.61 | -$102.05 | $897.95 | 1.13 | ||||||||||
| 9 | $299.32 | $17.96 | -$84.10 | $915.90 | 1.06 | ||||||||||
| 10 | $61.06 | $18.32 | -$65.78 | $934.22 | 1.30 | ||||||||||
| 11 | $93.42 | $18.68 | -$47.09 | $952.91 | 1.20 | ||||||||||
| 12 | $190.58 | $19.06 | -$28.04 | $971.96 | 1.10 | ||||||||||
| 13 | $64.80 | -$64.80 | -$92.83 | $907.17 | 1.30 | ||||||||||
| 14 | $106.73 | $18.14 | -$74.69 | $925.31 | 1.17 | ||||||||||
| 15 | $185.06 | $18.51 | -$56.18 | $943.82 | 1.10 | ||||||||||
| 16 | $94.38 | $18.88 | -$37.31 | $962.69 | 1.20 | ||||||||||
| 17 | $66.39 | $19.25 | -$18.05 | $981.95 | 1.29 | ||||||||||
| 18 | $47.90 | $19.64 | $1.59 | $1,001.59 | 1.41 | ||||||||||
| 19 | $87.09 | $20.03 | $21.62 | $1,021.62 | 1.23 | ||||||||||
| 20 | $38.55 | $20.43 | $42.05 | $1,042.05 | 1.53 | ||||||||||
| 21 | $148.86 | $20.84 | $62.89 | $1,062.89 | 1.14 | ||||||||||
| 22 | $78.73 | $21.26 | $84.15 | $1,084.15 | 1.27 | ||||||||||
| 23 | $49.28 | $21.68 | $105.83 | $1,105.83 | 1.44 | ||||||||||
| 24 | $157.98 | $22.12 | $127.95 | $1,127.95 | 1.14 | ||||||||||
| 25 | $161.14 | $22.56 | $150.51 | $1,150.51 | 1.14 | ||||||||||
Predictology | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N5 | N5 | =(J2*0.02)/(Z5-1) |
| O5:O25 | O5 | =IF(K5="WON",N5*(Z5-1),-N5) |
| P5:P25 | P5 | =SUBTOTAL(109,$O$5:O5) |
| Q5:Q25 | Q5 | =$J$2+P5 |
| N6:N25 | N6 | =(Q5*0.02)/(Z6-1) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| solver_adj | =Predictology!$Z$5:$Z$2985 | N5:O5 |
| solver_lhs1 | =Predictology!$Z$5:$Z$2984 | N5:O5 |
| solver_lhs2 | =Predictology!$Z$5:$Z$2984 | N5:O5 |
Column N is where the staking calculation is done. I thought of having the cells in N reference the cumulative profit, Column P, but again, in a filtered sheet, how can I keep this accurately calculating it based the current bank?
Any help gladly accepted.
