Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Formula mystery

  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Formula mystery-SOLVED

    I have 2 samples of formula from 2 different mathematics expert in the field of probability.

    The first formula A is stated as:
    [100/P] * 100/[[Q/[100-P]]*100/[[R/[100-Q]]*100]

    and the second formula B is stated as:
    [A[AB-1]*[C+1]]/[A+1]

    The probability is for the outcome of 3, but have included in my sample spreadsheet 11 because I am
    not really sure if it is for 3. (I think it is for 3)
    So the 1st 3 rank will be ok if that is the case.

    The problem is, these 2 formula don't come with instructions except Mr B say's that "Mr A's formula
    is for the purists"


    My problem is, I can't seem to make it work in the formula bar of excel.

    Hope someone can help with this

    Thanks in advance

    XP
    Last edited by XPDiHard; 2013-04-29 at 01:56. Reason: Protect some copyrights-abbreviated names

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XP,

    I'm looking at your workbook and I can't figure out what is what, I'm no Statistician.
    Basically you have to replace in the formulas the appropriate cell references, e.g.
    P=Cell Ref
    R=Cell Ref
    Q=Cell Ref

    A=Cell Ref
    AB= this is the tricky one as you'll use the ref for A about and mulitply it by B=Cell ref, thus (A*B) you'll need the extra () as this has to be done first!
    C=Cell Ref

    And of course all [] get replaced with (). HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    RG,
    That's a clue/start I can work from.
    Below is some information I found, the originator of the first formula is Dr R D,
    The problem is writing the formula, but below are the answers to the equation.
    It's the brackets he uses that I am not sure what it's suppose to be in Excel.
    In my workbook, all I did was show the 2 different formula, and some prices.
    Since posting I discovered it's for the top 3, not the entire 11.
    Below is Formula A examples, but cannot find any examples for formula B
    I'll keep researching and post if I find anything else.

    Dr R D in his book gave us the following formula

    {x * [(x*y-1)] *[z+1]/[x+1] -1

    where x is odds of winner, y is odds of 2nd placegetter and z is odds of 3rd placegetter.

    e.g. if x = $3.00 (2/1), y = $4.00 (3/1), z = $5.00 (4/1)

    2*[(2*3-1)]*[4+1]/[2+1] -1

    = [2*5*5/3]-1

    = 50/3 -1

    = 16.67 - 1

    = 15.67 to 1

    = $16.67
    Last edited by XPDiHard; 2013-04-29 at 01:49. Reason: Protect some copyrights-abbreviated name

  4. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    This next example is from another author in a book from the library, but still uses Dr R D's formula.
    The books are old by the way, I don't think they considered the invention of spreadsheets back in the 70's!
    Its the same formula, only written differently

    The formula by the originator was written as:
    X is the odds of the first placegetter. Y is the odds of the second placegetter and Z is the odds of the third placegetter.

    The following example may help your understanding. Let the odds of X = 2/1, Y = 3/1 and Z = 4/1


    x * [(x * y)-1] * [z+1]
    ----------------------- -1
    [x+1]


    => 2*5*5
    ---------- -1
    3

    => 50
    ------ -1
    3

    => 16.67 -1

    => 15.67/1

    (16/1 when rounded )


    What I need to arrive at is 15.67/1 in one cell and
    in another cell the rounded figure 16/1 when the examples are formulated as shown above
    There are no other books in the library in regards to Dr Dedman's formula.

    The only other example available is a very old program written in QBASIC on the basis of the Dr's formula.
    I have that in .BAS archived somewhere, it was typed out from another book to help with multiple scenarios over 15 years ago. But it's useless simply because it's in QBASIC.
    Hopefully one day I'll get it converted to Excel, solving this formula is the start of that very conversion.


    Thanks

    XP
    Last edited by XPDiHard; 2013-04-29 at 01:47.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In your first example (x = 10/1, y = 2.5/1 and z = 30/1) from cells J7-J9, the formula would be:
    =J7*(J7*J8-1)*(J9+1)/(J7+1)-1
    =675 [with the "/1" to be understood]

    The 2nd column K7:K9 is:
    =K7*(K7*K8-1)*(K9+1)/(K7+1)-1
    762

    Do the calculation and custom format to something like:
    0"/1"
    to display the 1

    Your other formula is invalid as the open (left) and close (right) brackets are not right. You need another close bracket somewhere or have an extra open. Based on the 100's in the formula, I suspect it is using the probabilitys not the odds, If the odds of winning are 3 (for) to 1 (against) the probability of winning is 3 (for) / [Total = 3(for) + 1(against)= = 3/4 = 0.75 = 75%

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    sdckapr

    Thanks,

    The 2nd column K7:K9 is:
    =K7*(K7*K8-1)*(K9+1)/(K7+1)-1
    762
    I get the correct answer as described in the book,
    that shows if the odds are;
    ------------
    A: 2/1
    B: 3/1
    C: 4/1
    Answer is: 15.67/1
    (16/1 when rounded)
    in Excel it shows as 15.66666667

    ------------
    That formula is Dr R D's.That is the answer he shows.
    What the argument is from the other guy, complimenting the Dr however,
    is it's "pure" that it suits Odds that are 1/1,1/2 etc...., or "Odds On".

    The other formula does not do "Odds On" calculations, that's the difference,
    or "un pure"
    Last edited by XPDiHard; 2013-04-29 at 01:49. Reason: Protect some copyrights-abbreviated name

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    My point remains that the 2nd formula is incorrect as it either is missing a right bracket or has too many left brackets. Also as I point out, if the 2nd formula does not work with "Odds on", you must transform the "odds on" values to work with what the formula needs, or adjust to formula to work with the odds.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    sdckapr,
    Point taken, the 2nd is not meant to work for odds on.

    I have amended the workbook showing the first formula.
    Now all I need do is sort out the 2nd formula, it may be a error on my
    behalf, will have to re-read the other book and check it again.
    Last edited by XPDiHard; 2013-04-29 at 01:56.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You have the formula1 (without the 100s) in the table listed for formula2.

    The formula2 (with the 100s) is clearly in error somewhere since the brackets do not match. What P,Q, and R refer needs to be explained/defined as well

    Also it does not make sense to me that the odds will yield negative results. If 4/1 indicates $4 would be won from a $1 bet, then the Odds of -1/1 suggests you if the horse won you would lose $1 for every dollar bet, and that is what I would expect if the horse lost, but I don't bet on horses so maybe I misunderstand what the meaning is.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Excel isn't really the issue here.

    The crux is that if you don't understand the formula you cannot trust the result.

    And just because you can make the numbers give an expected result for a specific example doesn't mean that they will work for every example.

    If you are doing this for fun - fine - but if you are going to take any significant financial risk based on the results of the formula then some work in understanding it is a definite requirement before representing it in Excel.

    Hope you find this helpful - it is meant to be !

  11. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    MartinM
    Excel isn't really the issue here.

    The crux is that if you don't understand the formula you cannot trust the result.
    I understand the formula, I know exactly what the authors are saying.
    What I don't know everything yet is how Excel works, that is why I am here, to ask and learn Excel do complex routines and calculations.

    The numbers that are proposed to be crunched is what I think a computer is all about!
    A glorified calculator.


    If you are doing this for fun - fine - but if you are going to take any significant financial risk based on the results of the formula then some work in understanding it is a definite requirement before representing it in Excel.

    Hope you find this helpful - it is meant to be !
    It's fun learning, and the risk is minimal, that I can assure you.
    Yes, every reply here is helpful,
    gratefully appreciated and hope I can contribute something back to.
    ------------,

    Ok,
    sdckapr
    You have the formula1 (without the 100s) in the table listed for formula2.

    The formula2 (with the 100s) is clearly in error somewhere since the brackets do not match. What P,Q, and R refer needs to be explained/defined as well

    Also it does not make sense to me that the odds will yield negative results. If 4/1 indicates $4 would be won from a $1 bet, then the Odds of -1/1 suggests you if the horse won you would lose $1 for every dollar bet, and that is what I would expect if the horse lost, but I don't bet on horses so maybe I misunderstand what the meaning is.

    Steve
    As I studied the book, formula 2 is far more complicated than first thought.
    This particular author is less clearer, he assumes one already knows how to use a spreadsheet.
    The other point I missed, he uses Lotus !
    And a TABLE I had to copy from his book.
    That table is now in Sheet 2

    His instructions are:
    " We are going to examine the prospects of 3 of the chances in Cells F1, F2, and F3 and are 4.00 for 1st
    $6.50 for second and $10.00 for third",

    and, we have other prices in cells G1, G2, and G3 and are, $3.50 for 1st, $14.00 for 2nd, and
    $25.00 for 3rd.

    Here is the problem...........the formula is Lotus, he goes on to say;

    Using Lotus notation......................................,

    @vlookup(f1,$a$1..$d$27,1)*@vlookup(f2,$a$1..$d27, 2)*@vlookup(f3,@1..$d$27,3)

    and

    @vlookup(g1,$a$1..$d$27,1)*@vlookup(g2,$a$1..$d$27 .2)*@vlookup(g3,$a1..$d$27,3)

    ...........when you run these numbers through the table you get


    Comb 1 ................$4.0 & 6.50 & 10.0 = 2.0 * 1.25 * 1.023 = 2.56

    Comb 2 ................$3.50 & 14.00 & 25.0 = 2.318 * 0.663 * 0.556 = 0.854

    Which tells you that Combination 1 is roughly 3 times more likely to succeed than Combination 2
    '''''''''''''''''''''''''

    I have uploaded the workbook with the table this author refers to in Sheet 2.

    What I need is the vlookup formula please.

    I have tried: ( I think this is the Excel version)

    =VLOOKUP(F2,$A$2:$D$27,1)*VLOOKUP(F3,$A$2:$D$27,2) *VLOOKUP(F4,$A2:$D$27,3)

    =VLOOKUP(G2,$A$2:$D$27,1)*VLOOKUP(G3,$A$2:$D$27,2) *VLOOKUP(G4,$A2:$D$27,3)

    The answer for combination 1, I get is 4.04, the author say's it's 2.56
    and for combination 2, I get 0.64, the same author say's it 0.854

    I have checked the tables in Sheet 2, 3 times and all is exactly as is from his book.

    There are no other instructions.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You want the formulas:
    =VLOOKUP(F2,$A$2:$D$27,2)*VLOOKUP(F3,$A$2:$D$27,3) *VLOOKUP(F4,$A2:$D$27,4)

    =VLOOKUP(G2,$A$2:$D$27,2)*VLOOKUP(G3,$A$2:$D$27,3) *VLOOKUP(G4,$A2:$D$27,4)

    since you are looking up the 2nd, 3rd, and 4th column in the table. The first column is the lookup value itself.

    Steve
    PS I still don't understand a situation where you would lose more money if the horses win than you would lose if the horses loss [though you would lose less if you didn't bet]. Does that actually occur in real life? Can you place a bet (say $100 dollars) and if the horses you bet win, they keep your money and come after you saying you owe $100 dollars more. I guess if you do the calculations you would realize this and not place the bet, but how many people would place bets and discover they owe MORE than they even bet when they choose the the right horses to win?

  13. #13
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks for the formula.


    PS I still don't understand a situation where you would lose more money if the horses win than you would lose if the horses loss [though you would lose less if you didn't bet]. Does that actually occur in real life? Can you place a bet (say $100 dollars) and if the horses you bet win, they keep your money and come after you saying you owe $100 dollars more. I guess if you do the calculations you would realize this and not place the bet, but how many people would place bets and discover they owe MORE than they even bet when they choose the the right horses to win?
    This is a study of probabilities, and in horse racing, "there are no certainties".
    I don't know if you are aware, these days you can bet on a horse to lose.
    It's termed, "lay betting".
    Then within that game there is arbitrage, you can bet on the same horse to win in one hour, wait and if the circumstances suit, you can bet on the same horse to lose and whatever the outcome, it's in profit.
    And it is not just horses, it can be tennis, football, coin tossing, almost anything these days, one can "arb their bets" and be in profit.
    The problem of course, is the cost of those softwares,data feeds, "bots" ( robotic scripts to search an arb situation), the list goes on and on.
    About betting 100 and winning then owing 100, I never heard of that before. A win is a win is a win, or a lay for that matter.

    XP

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote]This is a study of probabilities, and in horse racing, "there are no certainties".[/.quote]

    But in the results you claculate with -1/1 odds, there were a certainty that you would lose the $100 you bet. What was uncertain was if you would lose MORE money than the money you bet. If the horses lost, you would only lose the money you put up ($100). If the horses won, they would come to you and ask you to pay them $100 more.

    About betting 100 and winning then owing 100, I never heard of that before. A win is a win is a win, or a lay for that matter.
    That is how I would interpret odds of -1 / 1. How would you interpret "negative odds"?

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Ok, I see what you are getting at.

    Will this explain the answer ?
    http://en.wikipedia.org/wiki/Odds

    In particular;
    In gambling, the odds on display do not represent the true chances (as imagined by the bookmaker) that the event will or will not occur, but are the amount that the bookmaker will pay out on a winning bet, together with the required stake. For instance, if the bookmaker offers odds of 4:6 against a certain horse winning a race, this means that he'll accept a $6 dollar stake in return for a payoff of $4, plus return of the stake, if the horse wins. If the horse loses, the bookmaker keeps the stake.
    If not, I can ask.

    But if you are asking about the reference table where it goes into 0.968 for example, no, that is not a price, that's part of the statistical reference.
    The point is though, who's statistics ?
    Meaning, quality and accuracy and if they are the True Odds.

    I am aiming to form my own stats. So therefore the table will have different set of numbers, proven by actual results.
    Last edited by XPDiHard; 2013-04-29 at 11:15.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •