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
30 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies