Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel limited in a Monte Carlo?
I have developed a Monte Carlo simulation for Hockey games. It uses real world data inputs for the runs. The game is broken in to 3600 intervals (1 second time chunks) plus Over Time if needed. Each game has >100,000 cells with values and uses multiple worksheets.
Does excel or its RNG have a limit of what it can do?
All sim outputs are based off of probability using a known value and RNG. If the RNG exceeds the first value it moves onto the next one and so on until it either the RNG does not exceed the value or a Goal For is registered, essentially it is a building AND gate.
The problem I am running into is after 4 gates or more (Does a Corsi Event occur, does a Fenwick, does a Shot on Goal, does a Goal occur) the expected values over a large number of runs is not what is seen.
For example. After the first set of filters, it comes to one that can vary (based on its own OR gate and RNG functions) from one of 4 possibilities. These values can be from a 4.0% occurrence to 35.0% (this is Shooting percentage based on shot type). The overall Shooting percentage based on all shot types should be around 9.6% when all these 4 types are combined. I do get around 9.6% total SOG, but the variance between the shot types is also around 9.6% for all shot types, even if the probability is set to 4% or 30%, the end result is around the whole, which it should not be.
3 Answers
- 7 years ago
To continue, ughh
Since if a Goal (the four categories of concern here) is dependent on if a Shot on Goal (SOG) occurred and the probability that SOG becomes a goal. And if each of the 4 SOG types has different probabilities (4-30%) but I am getting 9-10% regardless of how many times it is sim'd which happens to be what the overall should be, have I hit a limit to what excel can do?
I ran into this same issue in this monte carlo in a different aspect, the outcomes where well outside expected (adding score effect and manning situations, added about 3 times as many calculating cells in the game sim).
I have verified the proper functions of ALL these issues when they are stripped into a smaller workbook, removing all other "variable" numbers that are the parents to them. The expected probabilities are met. It appears that I only run into this as more cells and levels are added and combined. Independent they function as expected, but together it appears excel is limited.
- 7 years ago
Dear Friend, thanks for thinking of me. However, i have never played a video game in my life. My Chilrens' have, and still do, .... even as they make grand babies for us. ( let your mind run with that one ...... !) But still, if you wanna talk about history and limited civil government, or fishing, and classic cars ....... keep me posted. Thanks alot. Cordially, john
