Results 1 to 15 of 24
Thread: Formula mystery

20130426, 21:37 #1
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 221
 Thanks
 80
 Thanked 3 Times in 2 Posts
Formula mysterySOLVED
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/[100P]]*100/[[R/[100Q]]*100]
and the second formula B is stated as:
[A[AB1]*[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
XPLast edited by XPDiHard; 20130429 at 01:56. Reason: Protect some copyrightsabbreviated names

20130426, 22:33 #2
 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 (). HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130427, 00:04 #3
 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*y1)] *[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*31)]*[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; 20130429 at 01:49. Reason: Protect some copyrightsabbreviated name

20130427, 00:30 #4
 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
XPLast edited by XPDiHard; 20130429 at 01:47.

20130427, 08:23 #5
 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 J7J9, the formula would be:
=J7*(J7*J81)*(J9+1)/(J7+1)1
=675 [with the "/1" to be understood]
The 2nd column K7:K9 is:
=K7*(K7*K81)*(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

20130427, 19:24 #6
 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*K81)*(K9+1)/(K7+1)1
762
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; 20130429 at 01:49. Reason: Protect some copyrightsabbreviated name

20130427, 19:52 #7
 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

20130427, 20:29 #8
 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 reread the other book and check it again.Last edited by XPDiHard; 20130429 at 01:56.

20130428, 05:45 #9
 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

20130428, 06:40 #10
 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 !

20130428, 08:49 #11
 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.
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 !
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
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.

20130428, 13:52 #12
 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?

20130428, 19:02 #13
 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?
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

20130429, 07:22 #14
 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.
Steve

20130429, 11:13 #15
 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.
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; 20130429 at 11:15.