Hey,
So I'm trying to make a formula calculate a value between some cells, but it seems quite complex and hard to explain. I'll do my best to explain while referencing to the attached image. On the table on the left, I'm trying to create a value for column "F" titled confidence. I want this value to reflect the bet reference confidence on the right table. For example, in C3, the reference is 1, so in F3, I want the value to be from the right table, all the values with the bet reference of 1 multiplied together. So the value in F3 would be R3*R4*R5. But I want the formula to connect the value by reference number if that makes sense. So every value on the right table with a reference number of 1, will be multiplied with the other values and put into the confidence column on the left table. This is a dodgy explanation, so I can answer clarifying questions. Thanks!
So I'm trying to make a formula calculate a value between some cells, but it seems quite complex and hard to explain. I'll do my best to explain while referencing to the attached image. On the table on the left, I'm trying to create a value for column "F" titled confidence. I want this value to reflect the bet reference confidence on the right table. For example, in C3, the reference is 1, so in F3, I want the value to be from the right table, all the values with the bet reference of 1 multiplied together. So the value in F3 would be R3*R4*R5. But I want the formula to connect the value by reference number if that makes sense. So every value on the right table with a reference number of 1, will be multiplied with the other values and put into the confidence column on the left table. This is a dodgy explanation, so I can answer clarifying questions. Thanks!
| dodgy book.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | |||||||||||||||||||||||
| 2 | Date | Game | Reference | Variables | Chance | Confidence | Confidence Chance | Advantage | Number Value | Result | Profit/Loss | Overall | Bet Reference | Player | Outcome | Chance | Confidence | Confidence Odds | Advantage | Result | |||
| 3 | 27-May | 1 | 3 | 3.25 | #DIV/0! | #DIV/0! | #N/A | #N/A | 1 | Variable 1 | To succeed | 1.19 | 0.85 | 1.18 | |||||||||
| 4 | 2 | 3 | 3 | #DIV/0! | #DIV/0! | #N/A | #N/A | 1 | Variable 2 | To succeed | 1.99 | 0.7 | 1.43 | ||||||||||
| 5 | 3 | 3 | 3 | #DIV/0! | #DIV/0! | #N/A | #N/A | 1 | Variable 3 | To succeed | 1.35 | 0.7 | 1.43 | ||||||||||
| 6 | 4 | 1 | 4 | #DIV/0! | #DIV/0! | #N/A | #N/A | 2 | Variable 4 | To succeed | 2.7 | 0.4 | 2.50 | ||||||||||
| 7 | #DIV/0! | #DIV/0! | #N/A | #N/A | 2 | Variable 5 | To succeed | 1.25 | 0.75 | 1.33 | |||||||||||||
| 8 | #DIV/0! | #DIV/0! | #N/A | #N/A | 3 | Variable 6 | To succeed | 1.54 | 0.7 | 1.43 | |||||||||||||
| 9 | #DIV/0! | #DIV/0! | #N/A | #N/A | 3 | Variable 7 | To succeed | 1.61 | 0.7 | 1.43 | |||||||||||||
| 10 | #DIV/0! | #DIV/0! | #N/A | #N/A | 3 | Variable 8 | To succeed | 1.23 | 0.7 | 1.43 | |||||||||||||
| 11 | #DIV/0! | #DIV/0! | #N/A | #N/A | #DIV/0! | ||||||||||||||||||
| 12 | #DIV/0! | #DIV/0! | #N/A | #N/A | #DIV/0! | ||||||||||||||||||
| 13 | #DIV/0! | #DIV/0! | #N/A | #N/A | #DIV/0! | ||||||||||||||||||
Sheet1 | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G3:G13 | G3 | =1/([@Confidence]) |
| H3:H13 | H3 | =([@Chance]-[@[Confidence Chance]])/[@[Confidence Chance]] |
| K3:K13 | K3 | =IFS(J3="win",[@[Number Value]]*[@Chance]-[@[Number Value]],[@Result]="loss",0-[@[Number Value]],[@Result]="void","0") |
| L3:L13 | L3 | =SUM($K$3:K3) |
| S3:S13 | S3 | =1/[@Confidence] |
