Not sure how to do a formula

dylancock

New Member
Joined
May 26, 2022
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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!

dodgy book.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2DateGameReferenceVariablesChanceConfidenceConfidence Chance AdvantageNumber ValueResultProfit/LossOverallBet ReferencePlayerOutcomeChanceConfidenceConfidence Odds AdvantageResult
327-May133.25#DIV/0!#DIV/0!#N/A#N/A1Variable 1To succeed 1.190.851.18
4233#DIV/0!#DIV/0!#N/A#N/A1Variable 2To succeed 1.990.71.43
5333#DIV/0!#DIV/0!#N/A#N/A1Variable 3To succeed 1.350.71.43
6414#DIV/0!#DIV/0!#N/A#N/A2Variable 4To succeed 2.70.42.50
7#DIV/0!#DIV/0!#N/A#N/A2Variable 5To succeed 1.250.751.33
8#DIV/0!#DIV/0!#N/A#N/A3Variable 6To succeed 1.540.71.43
9#DIV/0!#DIV/0!#N/A#N/A3Variable 7To succeed 1.610.71.43
10#DIV/0!#DIV/0!#N/A#N/A3Variable 8To succeed 1.230.71.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
RangeFormula
G3:G13G3=1/([@Confidence])
H3:H13H3=([@Chance]-[@[Confidence Chance]])/[@[Confidence Chance]]
K3:K13K3=IFS(J3="win",[@[Number Value]]*[@Chance]-[@[Number Value]],[@Result]="loss",0-[@[Number Value]],[@Result]="void","0")
L3:L13L3=SUM($K$3:K3)
S3:S13S3=1/[@Confidence]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Welcome to the MrExcel forum!

How about:

Excel Formula:
=PRODUCT(IF(Table2[Bet Reference]=[@Reference],Table2[Confidence],1))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top