dutching bets - help!

amoreira70 · Apr 24, 2014

hi there!

on image below you see a dutching calculator used for betting
I need the value 50€ to be distributed (based on the odds) to the bets (3 at image) so that the sum of values is equal to 50€ and all the odds multiplied for its valor result always in same value like on image 2 x 15 = 30; 6 x 5 = 30 etc

take in consideration that the higher the odd the lower the valor

can you please help me find a solution?

thanks

shg · Apr 24, 2014

There's a dutching calculator at https://app.box.com/s/5pt3sgepeuoj8q42lp36

amoreira70 · Apr 24, 2014

thanks but I'd rather try something less complicated and made with my own considerations
just wanted to fulfill the points I've stated before...

shg said:
There's a dutching calculator at https://app.box.com/s/5pt3sgepeuoj8q42lp36

amoreira70 · Apr 24, 2014

anyone?

shg · Apr 24, 2014

The workbook at the link does that. Using your example,
[Table="width:, class:grid"][tr][td] [/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][/tr]
[tr][td]

4

[/td][td][/td][td][/td][td]

Stake

[/td][td]

$ 50.00

[/td][td][/td][/tr]

[tr][td]

5

[/td][td][/td][td][/td][td]

To Win

[/td][td]

[/td][td][/td][/tr]

[tr][td]

6

[/td][td][/td][td][/td][td]

Win

[/td][td]

($ 20.00)

[/td][td][/td][/tr]

[tr][td]

7

[/td][td]

Winner

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8

[/td][td]Aposta 1[/td][td]

Wgt

[/td][td]

Dec Odds

[/td][td]

Bets

[/td][td]

Pays

[/td][/tr]

[tr][td]

9

[/td][td]Aposta 1[/td][td]

1

[/td][td]

2

[/td][td]

$ 15.00

[/td][td]

$ 30.00

[/td][/tr]

[tr][td]

10

[/td][td]Aposta 2[/td][td]

1

[/td][td]

6

[/td][td]

$ 5.00

[/td][td]

$ 30.00

[/td][/tr]

[tr][td]

11

[/td][td]Aposta 3[/td][td]

1

[/td][td]

1

[/td][td]

$ 30.00

[/td][td]

$ 30.00

[/td][/tr]

[tr][td]

12

[/td][td][/td][td][/td][td][/td][td]

[/td][td]

[/td][/tr]

[tr][td]

13

[/td][td][/td][td][/td][td][/td][td]

[/td][td]

[/td][/tr]

[tr][td]

14

[/td][td][/td][td]

**F

[/td][td]

(2.500)

[/td][td]

Total Bets

[/td][td]

Net

[/td][/tr]

[tr][td]

15

[/td][td][/td][td][/td][td][/td][td]

$ 50.00

[/td][td]

($ 20.00)

[/td][/tr]
[/table]

pgc01 · Apr 25, 2014

Hi
Welcome to the board

Shg's is a much more complete solution, but if you want just a solution to this specific problem

- define rOdds as the range with the odds

- in C2:

=IFERROR(INDEX(MMULT(MINVERSE(IF(ROW(rOdds)=MAX(ROW(rOdds)),1,IF(TRANSPOSE(ROW(rOdds))=MIN(ROW(rOdds)),INDEX(rOdds,1),IF(TRANSPOSE(ROW(rOdds))-ROW(rOdds)=1,-TRANSPOSE(rOdds),0)))),IF(ROW(rOdds)=MAX(ROW(rOdds)),$E$2,0)),ROWS($C$2:C2)),"")

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

Copy down

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">Total</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">15</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">50</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">30</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=7 style="background:#9CF; padding-left:1em" > [Dutch bet.xlsm]Sheet1</td></tr></table>

Remark: you should not post pictures with data, that you should know that can't be used. I only answered because I only had to use 4 values and you are new, or else you might not get any anwer.

pgc01 · Apr 25, 2014

Remark:

rOdds can be a dynamic named range that adjusts as you add or remove bets, as long as they are contiguous starting in B2, down:

Name: rOdds
Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$10,COUNT(Sheet1!$B$2:$B$10))

shg · Apr 25, 2014

Another way:

[Table="width:, class:grid"][tr][td] [/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][/tr]
[tr][td]

2

[/td][td][/td][td]

Total Bets

[/td][td]

$ 50.00

[/td][td][/td][/tr]

[tr][td]

3

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4

[/td][td]

Bet

[/td][td]

Odds

[/td][td]

Amount

[/td][td][/td][/tr]

[tr][td]

5

[/td][td]Bet 1[/td][td]

2

[/td][td]

$ 15.00

[/td][td]D5: =IFERROR($D$2 / (Odds * SUMPRODUCT(1 / Odds)), "")[/td][/tr]

[tr][td]

6

[/td][td]Bet 2[/td][td]

6

[/td][td]

$ 5.00

[/td][td]Odds Refers to: =INDEX($C:$C, ROW($C$4) + 1):INDEX($C:$C, MATCH(9E+307, $C:$C))[/td][/tr]

[tr][td]

7

[/td][td]Bet 3[/td][td]

1

[/td][td]

$ 30.00

[/td][td][/td][/tr]

[tr][td]

8

[/td][td]Bet 4[/td][td]