LEGEND
A1 ....... balance
B1-B4 .... multiplier (whatever B value is minus 1)
C1-C4 .... correct result for the formulas to be done
D1-D4 .... value aimed at .. D1,D2 ≈1 .. D3,D4 ≈0
E1-E4 .... D non-aproximated
See file for specific values.
The issue I have with set of formulas in C1-C4 is that all those must take into the account any change in the cells B1-B4 input manually on the go, or said differently any change B1-will affect the results in all cells C1-C4.
The aim is to achieve as close as possible or exact result in column D, a specified below:
+1 in D1 & D2 for formulas in cells C1 & C2 respectively, horizontally associated when multiplying C1B1 & deducting the SUM(C2,C3,C4)
when multiplying C2B2 & deducting the SUM(C1,C3,C4)
±0 in D3 & D4 for formulas in cells C3 & C4 respectively, horizontally associated
when multiplying C3xB3 & deducting the SUM(C1,C2,C4)
when multiplying C4xB4 & deducting the SUM(C1,C2,C3)
Again, the set of formulas C1-C4 must work in unison, finding the best possible balance between C1-C4 based on values in B1-B4 meanwhile achieving +1 in D1,D2 & ±0 in D3-D4 both
EDIT
Worksheet attached, updated to v0.2 .. with my attempt relevant to a set of 3x formukas only.
PS:
(added comments)
To make it practically senseful, this applies to sports betting.
Imagine a scenario where 4x individual bets are placed on the same event (eg. 0–0,1–1,2–2,3–3 .. or 0–0, 0–1, 1–0, 1–1 > whatever), thereof column B values are default odds (no control over this).
Now having these odds hard-keyed .. we'll focus on the only two things within our control, how much money & where; starting at the end by determing the bet outcome in case of a win.
(Minimum profit, aimed at, self-generates the lowest volatility overall .. & minimized expositions defining the bankroll requirement, in turn generate the best possible long term compounding effect, viewed as a profit proportIon vs the bankroll required itself ..
but this slready goes beyond the scope of this question/post).
This brings us to column D .. where we either make +1u on D1,D2 & ±0 on D3,D4 = so basically, we either make 1 unit or break even (in case of a win, or correct outçome backed). As you see, values D1,D2 are the product of CxB (wager*odd), deducted by the cost of other three wagers (either C2,C3,C4 or C1,C3,C4 respectively), as well as adding the oustanding balance (A, negative) value.
Same for D3,D4, but since we are only breaking even = voiding the cost of other wagers, only those are deducted off.
Now, we get to the crucial part, column C.
The resulting values or wagers are pretty much aligned according to the logic described above, however all 4 have to respond as unified, synchronized = each change of odds in column B (may that be only one, or all) shall create a newly resulting 'balance' between all C1-C4 .. to best align to achieve that +1.
Basically, if at odd 3.00 or payout 2:1 (=3-1) .. to make +1, I'd have to wager 0.5u; now every C wager has to account for the cost of other Cs too, so effectively, slightly higher .. & most importantly, all have to take inti account each other when meeting ±0,+1.
The trouble I experienced is that when I turned the equation around, even with writing the full equation including all Cs arranged within the same cell formula, I don't get the results in Ds as those intended!?
& I have no idea what the error might be.
Does this make sufficient irt perfect sense now?