Home

I have a challenging excel problem

Highlighted
Lyle Lazarus
New Contributor

Hi guys, 

Have got quite a challenging problem. 

I'm trying to build a model that produces the probability of horses winning a race. 
There are 4 "speedmaps" (i.e. scenarios of how the race might play out) and each scenario has a percentage of how likely it is to happen. There are 3 horses in the race. 

So, 
Speedmap 1 (happens 40% of the time)
Horse A wins 70%
Horse B wins 25%
Horse C wins 5%

Speedmap 2 (happens 30% of the time)
Horse A wins 30%
Horse B wins 50%
Horse C wins 20%

 

Speedmap 3 (happens 20% of the time)
Horse A wins 10%
Horse B wins 20%
Horse C wins 70%

Speedmap 4 (happens 10% of the time)
Horse A wins 80%
Horse B wins 10%
Horse C wins 10%

 

I manually worked out that for this race, the probability of each horse winning is: A = 47%, B=30%, C=23%


How do I build a model in excel where I can input any values in and the probability of the horses winning is spat out? 

 

Would appreciate any help/discussion!!
Thanks!

2 Replies

@Lyle Lazarus 

 

I think you need to give more information. What values are you entering? You can generate a random number between 1 and 10 and assign values of - 10,20,20,30,30,30,40,40,40,40 -. That would tell you which "Speed map" to use. So, I'm guessing, you get 3 friends to each select a horse. Then run the random number generator. Then that will show which horse won?

@Lyle Lazarus 

To get the figures you show either calculate the probability for each horse individually

= SUMPRODUCT( HorseA, Likelihood )

= SUMPRODUCT( HorseB, Likelihood )

= SUMPRODUCT( HorseC, Likelihood )

or as a single calculation

= MMULT( speedmaps, TRANSPOSE(Likelihood) )

[committed with CSE]

where 'Likelihood' is the probability for each speedmap.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies