Formula mystery

Topic

New Reply

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

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 !

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | 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

Reply | Quote

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.

Reply | Quote

The wiki article does not mention negative odds. I am referring to the calculations from Dedman’s formula which you calculated as -1/1 in your spreadsheet. For me “negative odds” just don’t make sense since you can’t have negative probabilities. [And in the case of -1/1 in your spreadsheet, the probability is undefined since the denominator is zero (the for is -1/0 and the against is +1/0), which suggests to me that the formula is inaccurate or not applicable to what you are doing in those cases]

But it is not a big point to me, but as long as you understand the results…

Steve

Reply | Quote

If you remove the -1 from the end of the formula, the rounded chance is 17/1

Which is a good question, what is the -1 ?
It’s been a very long time when I was new to this when I questioned the -1.
It’s been taken for granted and that’s what one does, leave the deep stuff to the professors in math.

This is not about placing money with a betting agency at -1 and owing them if it’s a losing event, no one would do that sort of deal, I know I wouldn’t.

Back to your question, and I am sorry for the long winded answers, I’m no teacher, obviously!
But,
If you break it down to a simple coin toss, it’s 50% chance of heads and 50% chance of Tails.
But there can be only 1 outcome.
50% -1

(J7*(J7:J8-1)*

Reply | Quote

I am not talking about the -1 in the formula. I was asking about the results which gave the odds as -1/1.

I am talking about the example you had in the spreadsheet, with the 3 odds were 1.1 /1, 1/1, and 1.5/1 and the result of the Dedman formula was -1/1.

Steve

Reply | Quote

That is correct, it does go into negative, where as the other formula stays at zero.

In the real world, there would never be such a race where the odds of 3 runners
are such.
1.1/1
1/1
1.5/1

I ran those figures through the QBASIC probability calculator to check, the answer is 0.

Reply | Quote

I suppose it is moot, but if the example you posted was not “real world”, I don’t see the point of the exercise. But as I stated before, as long as you understand the formula is meant to do, no one else here needs to.

Steve

Reply | Quote

as long as you understand the formula is meant to do, no one else here needs to.

. . . but I am uncomfortable simply programming something into Excel when I feel that the results may lead to incorrect conclusions being drawn. I tried to point this out earlier in this thread but was dismissed, and I don’t particularly want to get drawn into a long discussion of statistics.

Oh well . . . I’ve eased my conscience by writing this.

Reply | Quote

but I am uncomfortable simply programming something into Excel when I feel that the results may lead to incorrect conclusions being drawn.

I feel the same way. I don’t trust (eg) statistics programs unless I first generate my own values and conclusions to ensure they are comparable. I want to have an understanding of the methodolgies, assumptions, and limitations of the formulas that I use. Not all formulas are equivalent, though they may be acceptable under particular instances.[Kinetic Energy = mv² / 2 is a good example one learns in school, but is inaccurate when v is very large. The more accurate formula is E= mc² , but at lower (earthly) velocities they give “equivalent” results]

Steve

Reply | Quote

Precisely so, Steve !

Reply | Quote

Perhaps the examples I posted were not precise, it’s not the numbers I enter into the spreadsheet that’s important here.
The real crux of the matter was/is, the formula comes from books written ages ago. These books are old, but the numbers still stand, 1+1 will always = 2 whether you use an ancient Chinese abacus or the latest android.
All I asked was how the formula is structured to work in Excel and posted a few examples from those books. I may have made a few type errors or was not concentrating at the time. Sorry.

If you like more information and deeper studies with real world comparative analysis using real world figures, let me know and I’ll show you where to buy that particular book. It was written in 1991, but studies were done way before then, probably 70’s 80’s when there was no basic program for the layperson to crunch the numbers.
It is in that book where the entire program code is written in QBASIC. All one has to do is type in the code and presto, the formula works. The code is 214 lines. It has more pages of instructions, dos and don’ts.
The book does not say, “go learn to be a programmer”.
I’ll do better and send you or anyone interested the QBASIC file itself if one is willing to convert it to Excel VBA or show me how to convert it to Excel/VBA.
It defiantly needs an upgrade!

Reply | Quote