Ever wondered what a quarter of a million points might look like on an Excel chart?

I thought this was a nice visualization of the Monte Carlo method, so I decided to share it. It uses over 2 million formulas, so I used VBA to create the formulas when you open the workbook, which takes about 10 seconds on my machine, but saves over 50 MB of file size!

Try varying the number of darts and see how the accuracy of PI is affected. This shows quite clearly that high precision results with the Monte Carlo method require very large samples. Of course an infinite sample size would produce a perfect value for PI... whatever that means ;)

Or try hitting F9 and have the worksheet re-calc.

An interesting display happens when you have the number of darts set to the max (264,000) and you ALT-TAB to another window that completely covers the chart and then ALT-TAB back! Each flash is 32,000 points being drawn on the chart.

Here's the workbook:

If you liked this article, please share it!

This was a thought provoking example - thanks.

As it shows, Monte Carlo is resource intensive and so a variety of techniques have been invented to get more accuracy from a given sample. One approach here is to consider the specific geometry. If we divide the area into small slices of radius r and angle t (in radians), the area of each slice is A(sector) = r²t/2. So taking a small slice containing a point (x,y) we find that:

A(circular sector)/A(square sector) = max(x²,y²)/(x²+y²).

Averaging this quantity will clearly converge to A(cirle)/A(square)=pi/4. In tests this adds around an extra digit of accuracy to the result.

Also, another option for large plots is illustrated here:

http://img714.imageshack.us/img714/7545/mandelbrot.png

which is really just a data table with conditional formats but is surprisingly effective. Lori

@Lori -

Welcome back.

Glad you liked the post. I had actually thought about making the chart a quarter of a circle which I had figured would only need a quarter of the points (darts) to come to the same result. But in the end, I liked how the whole circle looked.

Thanks for the image for the beautiful mandelbrot!

Regards,

Daniel Ferry

excelhero.com

Yes, I like these examples that push the limits.

I should have been clearer in my second point though, I was trying to suggest that in order to overcome some of the charting limitations you could create a plot by using a large grid on the datasheet where each cell is just one pixel. In the mandelbrot example there are over 100,000 cells and 400 iterations per cell, giving 40 million cell calcs plus all the conditional formatting, but it still manages to update in several secs on my pc.

(BTW as i'm sure you're monitoring there's lots of junk links in recent comments.)

@Lori -

Any chance you could send me a copy of that spreadsheet? I'd love to see how it's done, first hand.

Spam is definitely a problem. I'm using MovableType 5.0 for this blog and it gets rid of most of the spam automatically. But unfortunately spam cleaning is a new daily chore of mine (actually, several times a day). I need to find a plugin for MT5 that does a better job...

Regards,

Daniel Ferry

excelhero.com

Sure, here's a link (329k): http://www.mediafire.com/?mzznvqfumfh

Response times could be further improved, on my setup:

Dragging on scroll bar to make selection ~ 3 secs

Zooming in/out with spinner control updates ~ 20 secs

Reducing iterations by deleting cells in columns A and B (in multiples of 10) and setting calculation to automatic except tables helps.

It's actually pretty straightforward, you can break it down by:

a) Selecting the whole sheet and pasting formulas to new workbook

b) Build data table with x and y as row and column inputs.

c) Copy and Paste sheet formatting to new workbook

The main trouble i had was in getting the controls to update independently without needing code. For this i used a trick with the lookup function which was kind of fiddly. Since then i found it's possible to make the references to the controls dynamic by using names such as x_scroll=offset(B315,zoom,0) which would allow you to create a table of scroll settings for each zoom value. I haven't had time to implement this however.