How to get this calculation into one formula? (betfair task)

[r_ivica]

Let me explain the situation how I get this calculation done, but in more steps. I want to know is it possible to get it in one step?

I have 3 teams in one football game and every team have some value (let's call them 'profit'):

ManUtd 40
Draw 40
Arsenal -60

Odds for those teams are:

ManUtd 2.72
Draw 2.66
Arsenal 3.80

As on Arsenal we have negative (-60) so we need to equal those 3 numbers (let's call them profits).
So we need to do this calculation: (3.80*26.31)-26.31= +73,668
--- where 26.31 is stake and formula for getting profit is: (stake*odds)-stake= profit

ManUtd 40-26.31 = 13.69
Draw 40-26.31 = 13.69
Arsenal -60+73.69 = 13.69

So now with those single calculation (bet) we get 'equal profit' 13.69

How to get this calculation automatically?

 

[r_ivica]

Another example. This time it becomes little different as one negative and two positive numbers here.

I have 3 teams in one football game and every team have some value (let's call those values 'profit'):

Lyon 102
Draw -30
Marseille -50

Odds for those teams are:

Lyon 2.72
Draw 2.66
Marseille 3.80

As on Lyon have postive and on Draw and Marseille negatives we need to equal those 3 numbers (let's call them profits).
So we need to do this 2 calculations:
1.) on Draw need to stake 50 to get equal profit (2.66*49.62)-50= +82,37
Now situation is:
Lyon 52.38
Draw 52.37
Marseille -99,62


--- where 49.62 is stake and formula for getting profit is: (stake*odds)-stake= profit
(note that we cannot get 100% exact profit but this is close enough!)

2.) 1.) on Marseille need to stake 40 to get equal profit (3.80*40)-40= +112
Now desired situation is:
Lyon 12.38
Draw 12.37
Marseille 12.38

 

[r_ivica]

Also second example. But this time for easier preview for you.
This time it becomes little different as one negative and two positive numbers here.

I have 3 teams in one football game and every team have some value (let's call those values 'profit'):

Lyon 102
Draw -30
Marseille -50

Odds for those teams are:

Lyon 2.72
Draw 2.66
Marseille 3.80

So it is like here:

Excel Workbook
	B	C	D	E	F	G	H	I
27	*	BACK	LAY	P/L	New bets	New back	New Lay	New P/L
28	ManUtd	2,72	2,74	102,00	*	0,00	0,00	102,00
29	Draw	2,66	2,68	-30,00	*	0,00	0,00	-30,00
30	Arsenal	3,8	3,85	-50,00	*	0,00	0,00	-50,00
Sheet3

As on Lyon have postive and on Draw and Marseille negatives we need to equal those 3 numbers (New P/L).
So we need to do this 2 calculations:
1.) on Draw need to stake 50 to get equal profit (2.66*49.62)-50= +82,37
Now situation is:
Lyon 52.38
Draw 52.37
Marseille -99,62

--- where 49.62 is stake and formula for getting profit is: (stake*odds)-stake= profit
(note that we cannot get 100% exact profit but this is close enough!)
(odds are only BACK odds, ignore LAY odds here).

So we have this now:

Excel Workbook
	B	C	D	E	F	G	H	I
27	*	BACK	LAY	P/L	New bets	New back	New Lay	New P/L
28	ManUtd	2,72	2,74	102,00	*	0,00	0,00	52,38
29	Draw	2,66	2,68	-30,00	49,62	49,62	0,00	52,37
30	Arsenal	3,8	3,85	-50,00	*	0,00	0,00	-99,62
Sheet3

2.) Next on Marseille need to stake 40 to get equal profit (3.80*40)-40= +112
Now desired situation is:
Lyon 12.38
Draw 12.37
Marseille 12.38

So now finally we got what we want:

Excel Workbook
	B	C	D	E	F	G	H	I
27	*	BACK	LAY	P/L	New bets	New back	New Lay	New P/L
28	ManUtd	2,72	2,74	102,00	*	0,00	0,00	12,38
29	Draw	2,66	2,68	-30,00	49,62	49,62	0,00	12,37
30	Arsenal	3,8	3,85	-50,00	40,00	40,00	0,00	12,38
Sheet3

Please help me with getting this caluclations automatically without need to manually find those 'New bets' to get equal P/L (profit/loss) here.

 

[Norie]

Why do you need/want it all in one formula?

Isn't it easier to take advantage of some helper columns/whatever for the intermediate stages of the calculation?

It should also be easier to maintain/change/check things if you used indivual columns/cells for each 'part' of the calculation.

 

[r_ivica]

Why do you need/want it all in one formula?

Isn't it easier to take advantage of some helper columns/whatever for the intermediate stages of the calculation?

It should also be easier to maintain/change/check things if you used indivual columns/cells for each 'part' of the calculation.

I don't need it all in one formula. Just would like to avoid manually input those 'New bets' and search for them manually each time. Would like excel to find those 'New bets' for me to get 'equal P/L' calculation.

 

[r_ivica]

I have found the solution for this
So in yellow 'new bets' area is displaying correct result (stakes)

Excel Workbook
	B	C	D	E	F	G	H	I
27	*	BACK	LAY	P/L	New bets	New back	New Lay	New P/L
28	ManUtd	1,35	1,36	140,00	0,00	0,00	0,00	77,02
29	Draw	5,4	5,5	-10,00	27,78	27,78	0,00	77,02
30	Arsenal	12,5	13	-300,00	35,20	35,20	0,00	77,02
Sheet3 (5)

This is solution for placing back bets. Now will try to get same result for back and lay bets calculation as those would give slightly bigger profit I believe.