Random outcome generator based on Probability

I'm new here and I was hoping one of the site experts would be able to help me out. Apologies in advance for any lack of etiquette.

So I'm trying to build a simple Excel random outcome generator based on probability. For example, lets say a horse race with 8 runners (ie. potential outcomes) let's call them Horse 1 - Horse 8 for simplicity.

Each horse has an implied probability of winning between 0% and 100%.

I want to be able to create a formula that will return a single "random" result in to a designated cell based on the probability entered for each horse prior to refreshing.

Is there a simple way to do this by putting the horses in column A, then the probabilities in column B and running the formula?

Thanks in advance.

Hi, welcome to the forum.

If I understand you correctly, you want to be able to enter a percentage chance of each horse winning, then have a formula which generates a winner, which changes whenever you update the chances?

This will do something like it:
1. Put your horse names in column A - Horse1, Horse2,..., Horse8.
2. Enter probabilities into column B - lets say 10,50,70,25,30,80,65,35
3. In C1, enter this:

Formula:

=RANDBETWEEN(B1-10,B1+10)

then drag that down to C8. This will generate a random number from 10 lower than the number you entered in B to 10 higher - so 50 could become anything from 40 to 60.
4. In D1, enter this:

Formula:

=INDEX(A1:A8,MATCH(MAX(C1:C8),C1:C8,0))

This will check which of the randomly generated numbers is highest and give you the name of the horse that matches.

The bigger the 'difference' you provide in step 2 (changing B1-10,B1+10 to B1-20,B1+20 for example), the more random the final result will be. The formula in step 2 won't ever return a negative number.

Every time you change something on the sheet, the formulae will update - if you want to re-run it without changing any of your numbers, press F9 to manually re-calculate.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thanks very much to both of you. I've implemented both methods and they both work nicely.

Just for your own amusement, the reason I'm doing this is because I have a few friends who like a bet on the horses, and every now and then convince themselves they've found a tipster who is the messiah. Knows what others don't, can tell the future etc etc!

I know that if there was any such person, who could consistently predict winners in any form of sport (especially horse racing), they would either keep very quiet about it and retire to the Bahamas before too long, or they would want a fortune for their services. They probably wouldn't be involved in excessive and highly suspicious forms of self promotion on Facebook, claiming they win thousands every day with "evidence" that a 5 year old with photoshop could fabricate in order to receive $10/month from punters for their knowledge. But when I point this out I am called cynical.

So I thought one way to debunk (or maybe prove!) the myth than by pitting the self proclaimed knowledge of this particular tipster with hundreds of thousands in claimed winnings, and years of experience, against a humble excel formula based on nothing more complicated than the book makers odds, and random chance.

I'd be happy to share the results with anyone who is interested!

You're welcome - good to see Excel being used in the cause of debunking bad statistics...

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.