Hey,
I'm not sure if this is the right section to share this, but I couldn't find another more suitable.
As we all know, the variance can greatly influence our results. I like to play around with the husng.com variance calculator to check if my current results seem OK, but this calculator doesn't provide an overall picture of the average results.
So I decided to implement my own HUSnG Profits Normal Distribution Calculator, using MS Excel. By running several simulations, it calculates and presents a normal distribution graph based on the results of the simulations.
You can download HUSnG Profits Normal Distribution Calculator here.
To use it, you just have to input the following data and press calculate:
I guess all the fields are pretty self-explanatory. Be aware that if you set a very high number of simulations, it can take a while to perform the calculations. If you have already played a lot of games you can reduce the number of simulations, since more games reduces the variance.
After you press calculate, the Results table and the Normal Distribution graph is calculated:
How to interpret the results:
As you can see in the static graph on the lower left corner, most of the population results (68.2%) will fall between the (average - 1 standard deviation) and (average + 1 standard deviation). In this case, most of the population with a winrate of 55% will have, after playing 279 $7 games, a profit between -$12.2 and $206.1. As you can see, the effect of the variance on your final results is huge! My current profit is around $20, so it is still likely that my winrate is 55%, despite my current mediocre results.
Please tell me if you find this tool useful, and if you have any questions or suggestions, just shoot it! :)
Looks great, will have to try.
Thanks for sharing.
Go forth and CRUSH !
Hey man, cool simulation tool :)
Couple of bugs when I run it on my machine (suspect you might have custom functions set up which are not defined in the workbook you posted?)
1. In Module 1, in the standard deviation calc the StDev_S function is not defined by default
stddev = WorksheetFunction.StDev_S(Sheets("RandomNumbers").Range(resultsRange))
change to
stddev = WorksheetFunction.StDev(Sheets("RandomNumbers").Range(resultsRange))
2. On 'Aux' Sheet range (B7:B66), _xlfn.NORM.DIST function is not defined by default
=_xlfn.NORM.DIST(A7,$B$1,$B$2,FALSE)
change to
=NORMDIST(A7,$B$1,$B$2,FALSE)
=> alternatively you could hardcode the worksheet function within the module like you did for other calcs (in case people have auto calcs turned off)
Cheers,
cdon
Hi cdon,
Thanks for the bug report. I don't have any custom functions defined, so it must be due to incompatibilities between Excel versions. I'm using Excel 2013, what's yours?
Anyway, I updated the workbook with the corrections you suggested, as your corrections also work on my excel version.
In fact, this is the first time I programmed in excel, and I was having trouble injecting formulas into the sheets... So I ended up hardcoding some simple formulas (although my first goal was to use excel formulas whenever possible). Then I discovered how to use a worksheet formula in a macro, but I didn't change the code that I had done previously, and that's why some functions are hardcoded and others are not :P
He who makes a beast of himself gets rid of the pain of being a man. -- Samuel Johnson
Yer might be a compatibility issue. I'm using excel 2007.
Hardcoding worksheet functions using VBA can be really useful if you're doing calculations on large volume of data.
I had a workbook that got to > 100MB (lolwat!) and was crashing all the time because I was running mass calcs via lookup tables.
By running the calcs in VBA and just using the worksheet to do basic functions it ran a lot faster, stopped crashing and dramatically reduced the file size.
Coding the population of random numbers in VBA was a good idea too because otherwise they repopulate every time you recalculate the sheet (which happens automatically if you change a variable and haven't toggled auto calcs off).
This is quite impressive for your first time at excel!
I know guys that work with excel every day that couldn't create this.
100MB worksheet!? Wow, it should take ages just to open that...
Actually I intended to do populate the random numbers using the RANDBETWEEN formula, but I came across the problem you described: it was recalculating the random numbers every time a cell was edited... So I switched it to VBA.
It was my first time programming in excel and VBA, but I'm a software engineer, so it wasn't too difficult
He who makes a beast of himself gets rid of the pain of being a man. -- Samuel Johnson
The 100MB workbook was for poker modelling and it struggled to even open once it got too large let alone recalculate.
I ended up redesigning large components of it and am still (albeit somewhat passively around hectic work schedule currently) giving a lot of thought to how I can run the sensitivity analysis more efficiently.
I encountered the random number issue when I built a variance / BRM simulator a while ago.
I sent you a PM with my skype details.
Would be cool to bounce ideas off each other if you're interested.
Works fine now in Excel 2011 for Mac.
Thanks very much
"If you want to win, you must not lose!"
Cool, good to know it works on Mac ;)
Hey, no problem, you guys from husng.com already helped me a lot, so I can only give something back ;)
He who makes a beast of himself gets rid of the pain of being a man. -- Samuel Johnson