Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Coherently-adjusting set of formulas based on their results

0

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 C2
B2 & 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?

Answer
Discuss

Discussion

Hello and welcome.
First, I recommend you change you nickname since a nickname such as yours is typically not allowed on this forum. Don't be surprised if you are contacted by Don (owner of the site) asking/telling you to change it.
Now as for you question. I have read and re-read your post, and reviewed your sample file, but I am still not clear as to what you are hoping to achieve. In your sample file you state that C1-C4 are to be formulas but the sample file values in C1-C4 and simply hard keyed. How are these values arrived at? Which cells do they use to arrive at these values?
WillieD24 (rep: 537) May 31, '23 at 11:40 pm
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 volatily overall .. & minimized expositions defining the bankroll requirement, in turn generate the best possible long term compounding effect, viewed as aprofit proportIon vs the bankroll required itself .. 
 but this slready goes beyond the scope of this question/post).
SexMob Jun 1, '23 at 7:21 am
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.
SexMob Jun 1, '23 at 7:24 am
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 = 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 mzke +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?
SexMob Jun 1, '23 at 7:25 am
Yes, it does make a bit more sense, but i will need to review this a few times to fully digest it. It seems that someone who is well versed in statistics (which I am not) would be better at finding a solution for you. I will look at this again (later today or tommorow) but I make no promise that I will have a solution. I will let you know.
WillieD24 (rep: 537) Jun 1, '23 at 9:11 am
OK.
SexMob Jun 1, '23 at 3:36 pm
Hi ZTE,
I've read and re-read your descriptions, and reviewed your revised file (v0.2). I am at a loss to understand why you would want to calculate bets to result in a break-even across 4 bets. Or maybe I am not fully understanding your goal of this file. Whichever the case, I'm going to "tap out" on this one. Sorry. Hopefully there is another forum member who can help you out.
WillieD24 (rep: 537) Jun 3, '23 at 4:44 pm
Whatever, its clearly written in text & formulas that two formulas aim at +1, & two at breakeven.

You are not to slow to comprehend that, meaning I am not buying your pretentious act, nor your attempt at discreting me with your misinterpretation.

& moreover, my name is none of your business. So don't probe. & if you feel insulted in any way, I suggest you sort out your mind allergies.
SexMob Jun 3, '23 at 5:11 pm
Bottom line, stop posing.
SexMob Jun 3, '23 at 5:14 pm
& trying to waste time.
SexMob Jun 3, '23 at 5:14 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login