Variable dutching calculator

jt42cwrm

New Member
Joined
Aug 16, 2007
Messages
15
Hello,

I am struggling to figure out how to go about this, perhaps someone can point me in the right direction?

I wish to create a variable dutching calculator whereby, instead of all the runners in the field generating the same profit, one can be weighted to more than the rest. For example, Mr Ed's return to be 200% of all the other runners, whilst the overall stake remains constant.

Here is my standard dutching calculator:-

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #A6A6A6;;">Winner</td><td style="font-weight: bold;background-color: #A6A6A6;;">Price</td><td style="font-weight: bold;background-color: #A6A6A6;;">% chance</td><td style="font-weight: bold;background-color: #A6A6A6;;">% stake</td><td style="font-weight: bold;background-color: #A6A6A6;;">Stake</td><td style="font-weight: bold;background-color: #A6A6A6;;">Gross earnings</td><td style="font-weight: bold;background-color: #A6A6A6;;">Net earnings</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Mr Ed</td><td style="text-align: right;;">12.5</td><td style="text-align: right;;">8.00%</td><td style="text-align: right;;">45.37%</td><td style="text-align: right;;"> £ 3.18 </td><td style="text-align: right;;"> £ 32.75 </td><td style="text-align: right;;"> £ 31.11 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Silver</td><td style="text-align: right;;">65.0</td><td style="text-align: right;;">1.54%</td><td style="text-align: right;;">8.72%</td><td style="text-align: right;;"> £ 0.61 </td><td style="text-align: right;;"> £ 32.65 </td><td style="text-align: right;;"> £ 31.02 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Trigger</td><td style="text-align: right;;">30.0</td><td style="text-align: right;;">3.33%</td><td style="text-align: right;;">18.90%</td><td style="text-align: right;;"> £ 1.32 </td><td style="text-align: right;;"> £ 32.60 </td><td style="text-align: right;;"> £ 30.97 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Quick Draw McGraw</td><td style="text-align: right;;">21.0</td><td style="text-align: right;;">4.76%</td><td style="text-align: right;;">27.00%</td><td style="text-align: right;;"> £ 1.89 </td><td style="text-align: right;;"> £ 32.69 </td><td style="text-align: right;;"> £ 31.06 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Total stake</td><td style="text-align: right;;"> £ 7.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Commission</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=1/$B2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=$C2/SUM(<font color="Blue">$C$2:$C$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=ROUND(<font color="Blue">$D2*$B$7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=(<font color="Blue">$E2*$B2</font>)-SUM(<font color="Blue">$E$2:$E$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=ROUND(<font color="Blue">$F2-(<font color="Red">$F2*$B$8</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=1/$B3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">=$C3/SUM(<font color="Blue">$C$2:$C$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E3</th><td style="text-align:left">=ROUND(<font color="Blue">$D3*$B$7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=(<font color="Blue">$E3*$B3</font>)-SUM(<font color="Blue">$E$2:$E$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=ROUND(<font color="Blue">$F3-(<font color="Red">$F3*$B$8</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=1/$B4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D4</th><td style="text-align:left">=$C4/SUM(<font color="Blue">$C$2:$C$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=ROUND(<font color="Blue">$D4*$B$7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=(<font color="Blue">$E4*$B4</font>)-SUM(<font color="Blue">$E$2:$E$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=ROUND(<font color="Blue">$F4-(<font color="Red">$F4*$B$8</font>),2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=1/$B5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=$C5/SUM(<font color="Blue">$C$2:$C$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E5</th><td style="text-align:left">=ROUND(<font color="Blue">$D5*$B$7,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=(<font color="Blue">$E5*$B5</font>)-SUM(<font color="Blue">$E$2:$E$5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=ROUND(<font color="Blue">$F5-(<font color="Red">$F5*$B$8</font>),2</font>)</td></tr></tbody></table></td></tr></table><br />

Can anyone give me any suggestions?
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
Can't you just enter the horse twice?
 

jt42cwrm

New Member
Joined
Aug 16, 2007
Messages
15
That would increase my total stake, which I want to remain the same. Plus I might want to set the variable return on that one "horse" to anything, ranging from 0% to 500% the return on the other runners.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
See https://www.box.com/s/5pt3sgepeuoj8q42lp36

I differs from what you asked for in that you choose a base amount to win, and the weights multiply that, rather than specifying your stake.

For example,

Code:
       -------B------- -C- ---D---- ----E----- ---F----
   2                       To Win W  $50.00            
   3                                                   
   4   Mud in Your Eye Wgt Dec Odds    Bets      Pays  
   5   Mud in Your Eye   1      10     $ 11.99 $ 119.86
   6   Hooves of Steel   2       8     $ 21.23 $ 169.86
   7   Speeding Bullet   3       6     $ 36.64 $ 219.86
   8   Rein Me In                                      
   9   Go Bob Go                                       
  10                          0.392 Total Bets   Net   
  11                                   $ 69.86  $ 50.00


Code:
       -------B------- -C- ---D---- ----E----- ---F----
   2                       To Win W  $50.00            
   3                                                   
   4   Hooves of Steel Wgt Dec Odds    Bets      Pays  
   5   Mud in Your Eye   1      10     $ 11.99 $ 119.86
   6   Hooves of Steel   2       8     $ 21.23 $ 169.86
   7   Speeding Bullet   3       6     $ 36.64 $ 219.86
   8   Rein Me In                                      
   9   Go Bob Go                                       
  10                          0.392 Total Bets   Net   
  11                                   $ 69.86 $ 100.00


Code:
       -------B------- -C- ---D---- ----E----- ---F----
   2                       To Win W  $50.00            
   3                                                   
   4   Speeding Bullet Wgt Dec Odds    Bets      Pays  
   5   Mud in Your Eye   1      10     $ 11.99 $ 119.86
   6   Hooves of Steel   2       8     $ 21.23 $ 169.86
   7   Speeding Bullet   3       6     $ 36.64 $ 219.86
   8   Rein Me In                                      
   9   Go Bob Go                                       
  10                          0.392 Total Bets   Net   
  11                                   $ 69.86 $ 150.00
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I made a change so you can enter either the stake or the amount to win. Same link.
 

jt42cwrm

New Member
Joined
Aug 16, 2007
Messages
15
Wow shg, thanks!
I haven't seen or used any mathematical equations like those for over 20 years so my head hurt just looking at them.

It does the trick perfectly, cheers.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,816
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, good luck.
 

flavoz

New Member
Joined
Aug 14, 2017
Messages
1
Really like what you've achieved with the Variable Dutch Book. Is it possible to go a step further and provide for a "saver" bet, i.e., bet an amount to break even on the "saver" bet and enable an increase in the amounts bet on remaining selections?
Also, do you have any views on how to convert a Dutching spreadsheet to a stand alone program?
 

Forum statistics

Threads
1,144,278
Messages
5,723,467
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top