|
If you decide to create this spreadsheet from scratch you will find that it involves a large amount of work. My advice is to use the link below and save yourself all that typing. |
In this project I do not intend to explain how complex numbers work (on which the Mandelbrot set is based), nor do I intend to explain the formula which is used to create the set from those complex numbers. Both these are explained elsewhere on the net (and possibly even elsewhere on my site, if I have got round to it!) Instead, I will explain how to turn the Mandelbrot set into a Excel spreadsheet.
In practice, we can't plot an infinite number of data points. The screen resolution simply won't permit it, and the processing power required would extend infinitely. We usually limit ourselves to a rectangular array of a certain number of columns of dots and a certain number of rows of dots. In the case of this spreadsheet, I have limited myself to a grid which is 21 points by 21 points. This gives poor resolution, but I just wanted to demonstrate the principle - you can always add more columns and rows later if you are willing to put in the work!
If you look in the top-left corner of the spreadsheet you will notice a minimum and maximum value for both x (the horizontal co-ordinate across the grid) and y (the vertical component down it). I have set these to values which give the maximum possible size for the whole set on the screen. Please do change them - the whole point of the Mandelbrot set is to view it at different scales.
The 21 points are spread horizontally from the minimum X value to the maximum X value. You can see each of these horizontal values in row 1, extending from cell D1 (which should match the minimum X value in B2) to AR1 (which matches the maximum X value in B3). Similarly, the 21 vertical points are spread vertically in column C with the minimum Y value in C2 and the maximum Y value in C182.
The formula used to calculate the intervening points takes the previous point and adds 1/20 of the the range between the minimum and maximum values. For instance, if the minimum X value were -2 and the maximum X value were +2, then the range between them would be 4 (from -2 to 2) and it would be necessary to add on 4 / 20 = 0.2 each time. Although there are 21 values, it is only necessary to add on the step value 20 times (to see why this is the case, imagine that you have 21 fence posts. How many fence panels could you string between them?)
Each point comprises a complex number, which will contain a real and an imaginary part (two numbers requiring two cells). This is why each step value is spaced two columns apart in row one. Similarly, the complex numbers will require several calculations to be done on them, and for this reason I have left 8 lines between one vertical value in column C and the next. The number 8 in this case is rather arbitrary - the calculations that we will need to find the Mandelbrot set I have chosen to perform 8 times. You may need to increase this as you improve the resolution of the graphic.
The orange borders and ==== separators I have just inserted for clarity, so that we can easily spot where one calculated point ends and the next one starts. As you can appreciate, with a spreadsheet of this size, crammed full of figures, it is easy to lose track as to which cell belongs where. I have chosen to put line borders round the groups of cells for the same reason.
Each formula takes the value in the previous column and adds one twentieth of the total range in X values. The formula that you see above was taken from F1 (i.e. it adds one twentieth of the range to the value in D1).
A similar story takes places down the columns, except that this time the row values have much larger spaces between them on the spreadsheet (although the spacing in the environment of real and imaginary numbers is initially smaller: -1.2 to +1.2). The formula in C11, for instance is
i.e. it adds one twentieth of the vertical range on to the previous value (in C2). Between them, the cells along the top row and the ones down column C (i.e. the cells with the yellow background) cover the entire range of X and Y.
 |
Now let's consider what we do with those figures. The diagram on the left shows one pair of X and Y co-ordinates being processed, corresponding to a single coloured point on the Mandelbrot set itself. The entire calculation for this point on the graph is enclosed within the cells with the black border.
The calculation should really be repeated on the point many times. I have chosen to repeat it just 8 times, as shown by each of the lines in the box. Each step in the calculation produces a new complex number, with a real and an imaginary part, so we need two cells to represent the complex number at each stage. |
The first line of the group of cells is slightly different from the others below it. The two cells in the first line are based on the cells with the yellow backgrounds as shown. The cells below that process the figures in the cells above them. Since the calculation in these cells is basically a repeat of the ones above, I made good use of the Fill Down facility here.
Back to the menu