I have a challenging excel problem

%3CLINGO-SUB%20id%3D%22lingo-sub-392690%22%20slang%3D%22en-US%22%3EI%20have%20a%20challenging%20excel%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392690%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20got%20quite%20a%20challenging%20problem.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI'm%20trying%20to%20build%20a%20model%20that%20produces%20the%20probability%20of%20horses%20winning%20a%20race.%26nbsp%3B%3CBR%20%2F%3EThere%20are%204%20%22speedmaps%22%20(i.e.%20scenarios%20of%20how%20the%20race%20might%20play%20out)%20and%20each%20scenario%20has%20a%20percentage%20of%20how%20likely%20it%20is%20to%20happen.%20There%20are%203%20horses%20in%20the%20race.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%26nbsp%3B%3CBR%20%2F%3ESpeedmap%201%20(happens%2040%25%20of%20the%20time)%3CBR%20%2F%3EHorse%20A%20wins%2070%25%3CBR%20%2F%3EHorse%20B%20wins%2025%25%3CBR%20%2F%3EHorse%20C%20wins%205%25%3CBR%20%2F%3E%3CBR%20%2F%3ESpeedmap%202%20(happens%2030%25%20of%20the%20time)%3CBR%20%2F%3EHorse%20A%20wins%2030%25%3CBR%20%2F%3EHorse%20B%20wins%2050%25%3CBR%20%2F%3EHorse%20C%20wins%2020%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESpeedmap%203%20(happens%2020%25%20of%20the%20time)%3CBR%20%2F%3EHorse%20A%20wins%2010%25%3CBR%20%2F%3EHorse%20B%20wins%2020%25%3CBR%20%2F%3EHorse%20C%20wins%2070%25%3CBR%20%2F%3E%3CBR%20%2F%3ESpeedmap%204%20(happens%2010%25%20of%20the%20time)%3CBR%20%2F%3EHorse%20A%20wins%2080%25%3CBR%20%2F%3EHorse%20B%20wins%2010%25%3CBR%20%2F%3EHorse%20C%20wins%2010%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20manually%20worked%20out%20that%20for%20this%20race%2C%20the%20probability%20of%20each%20horse%20winning%20is%3A%20A%20%3D%2047%25%2C%20B%3D30%25%2C%20C%3D23%25%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHow%20do%20I%20build%20a%20model%20in%20excel%20where%20I%20can%20input%20any%20values%20in%20and%20the%20probability%20of%20the%20horses%20winning%20is%20spat%20out%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20help%2Fdiscussion!!%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-392690%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EModel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393962%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20a%20challenging%20excel%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F217181%22%20target%3D%22_blank%22%3E%40Lyle%20Lazarus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20the%20figures%20you%20show%20either%20calculate%20the%20probability%20for%20each%20horse%20individually%3C%2FP%3E%3CP%3E%3D%20SUMPRODUCT(%20HorseA%2C%20Likelihood%20)%3C%2FP%3E%3CP%3E%3D%20SUMPRODUCT(%20HorseB%2C%20Likelihood%20)%3C%2FP%3E%3CP%3E%3D%20SUMPRODUCT(%20HorseC%2C%20Likelihood%20)%3C%2FP%3E%3CP%3Eor%20as%20a%20single%20calculation%3C%2FP%3E%3CP%3E%3D%20MMULT(%20speedmaps%2C%20TRANSPOSE(Likelihood)%20)%3C%2FP%3E%3CP%3E%5Bcommitted%20with%20CSE%5D%3C%2FP%3E%3CP%3Ewhere%20'Likelihood'%20is%20the%20probability%20for%20each%20speedmap.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393902%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20a%20challenging%20excel%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393902%22%20slang%3D%22en-US%22%3E%3CP%3E-ERR%3AREF-NOT-FOUND-%40Lyle%20Lazarus%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20need%20to%20give%20more%20information.%20What%20values%20are%20you%20entering%3F%20You%20can%20generate%20a%20random%20number%20between%201%20and%2010%20and%20assign%20values%20of%20-%2010%2C20%2C20%2C30%2C30%2C30%2C40%2C40%2C40%2C40%20-.%20That%20would%20tell%20you%20which%20%22Speed%20map%22%20to%20use.%20So%2C%20I'm%20guessing%2C%20you%20get%203%20friends%20to%20each%20select%20a%20horse.%20Then%20run%20the%20random%20number%20generator.%20Then%20that%20will%20show%20which%20horse%20won%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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?

Highlighted

@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.