The Excel Mandelbrot
I wrote my first Excel Mandelbrot program in the late 1990’s, using VBA. But it was in a business meeting in the early 2000’s where I had questioned the need to get develop and application, where I questioned “Why do that, when we could easily do that in Excel”. The salesman gave me a spiel about why you couldn’t do that do that in Excel, with me retorting “You can do anything in Excel”. He immediately responded “You can’t make a Mandelbrot in Excel without using VBA”.
Challenge accepted !
A Mandelbrot is a contour plot of the count of the escape iterations of the formula Z2=Z2+1
Areas shaded black, the iterations never escape a predefined value, areas of similar color outside the black area, take a similar number of iterations, before exceeding that predefined value, ie: escaping the Mandelbrot. Mandelbrots are fascinating constructs with many one remarkable feature of the Mandelbrot being that it has a defined area, but an infinite length boundary.
The Mandelbrot is calculated by solving the equation Z2=Z2+1
Z is calculated via a formula that is recursive, ie: it feeds on its previous values.
This value of Z is equal to a previous value of Z via some function, in this case Znew2=Zold2+1
Z is also a complex number, which is typically shown as Z = X + Yi, where i is the square root of -1
The Mandelbrot equation can be solved in the real / orthogonal plain using X & Y values iteratively.
The Mandelbrot is the calculation of a solution to the equation, Z2=Z2+1.
This can be performed on the orthogonal X-Y plane, where the X & Y values form the starting location for a solution, which is then iterated through the formula, keeping count of the number of iterations to achieve or exceed a predefined limit.
You can follow along using the Mandelbrot file using the link below
Effectively you need to have 3 loops inside each other, which can be done within Excel with a bit of thinking and smart use of Excel Functions.
We can use the Rows of a worksheet to do the iterations of one set of starting X & Y values. The other two loops that feed the initial X and Y values are souced iteratively using a Data Table function. that will be described in a section below
This is shown below:
1 – Starting coordinates, X & Y values, these will be updated via a 2 dimensional Data Table
2 – Transfer starting values into the iteration area
3 – The starting values become the old X & Y Values
4 – Calculate the new X & Y values using the formula
Xnew = Xold2 – Yold2 +Xorig
Ynew = 2 x Xold x Yold +Yorig
5 – Calculate the Rsq value, or the distance of the new point from the origin
Rsq = Sqrt( Xnew2 + Ynew2 )
6 – If the RSq value (7) is not greater than the Escape Value (8) repeat steps 3, 4 & 5 above
7 – When the Rsq value (7) is greater than the Escape value (8) stop iterations and determine the count in this example it is 8
So we have determined the Count value 8 for our starting position of x=-1.0, y=0.4
you can examine the table of Xold and Yold values above and see how they change over the various iterations.
Once the Rsq value is greater than 4, subsequent iterations can never be less than 4 and the iterations have escaped the Mandelbrot, in this case in 8 steps.
This is shown graphically
We now need to do that for each set of x, y coordinates in the range x = -2 to 1, y= -1.5 to 1.5
To achieve this we can setup a 2 Dimensional Table, and then use the Excel, Data Table function to feed the results into the Xorig and Yorig positions.
This is shown below
1 – A 2 dimensional data area, yellow, is setup. the column headers contain the x coordinates from -2 to 1 and the row headers contain the y coordinates from -1 to 1.
The Excel Data Table function is used to put the Column & Row headers in order into the Xorig and Yorig positions
ie: The Data Table is setup using the Data, What If Analysis, Data Table menu and entering the cell references for the Xorig and Yorig cells
2 – The Data Table function iterates through every cell within the Data Table, Yellow, area sending the Row and Column headers values to the Xorig and Yorig cells $F$10 and $F$11 respectively
3 – The Data Table function recalculates the worksheet and sends the Count, 2 in this case, back to the Data Table Upper Left cell H222.
4 – The Data Table function then stores that value in the Data Table position at the intersection of the Column and Row ie: Cell J225
The data Table function continues iterating through each of the yellow cells, repeating steps 1 to 4 above.
Once the Data Table function has completed iterating through the Mandelbrot data table area you can zoom in on parts of the table and can see they contain the Counts of the escape iterations as defined above
You can start to visualise the shape of the contours of the Count by following areas of constant values
However Excel has some charts that are suitable for displaying contour data, the Surface, Contour chart type.
Excel contains a number of native functions that handle complex numbers, ie:
You can read more about the Excel use of complex numbers at:
COMPLEX() – Converts the component values of a complex number to a complex number
IMSUM()– Sums or adds up complex numbers
IMPOWER() – Calculates the complex number raised to some power
IMABS() – Calculates the absolute value of an complex number, ie the square root of the complex numbers components squared
I had always wondered whether we could use the Complex math functions within Excel to calculate the Mandelbrot directly.
Well wonder no more, the answer is Yes it can.
You can follow along using the Complex Mandelbrot file from the link below
By using the Excel Complex functions, the math is simplified at the expense of speed.
The Excel complex functions are incredibly slow compared to the native excel functions which in most parts are highly optimised for speed.
Lets examine how this Mandelbrot works using the Excel’s complex functions
The first thing to note is that we now do not require the Xold, Xnew, Yold and Ynew columns
The data is all contained within the z column, and each row uses the previous row for it’s iteration calculations.
Using the same inputs as above x = -1.0 and y = 0.4, these are stored in G12:G13 respectively.
Cell E18 converts the Xorig and Yorig values into a complex number using the formula:
E18: =COMPLEX(G12, G13)
Excel displays complex numbers as a text string, ie: Cell E18: displays -1+0.4i representative of the complex format of -1x +0.4y starting position of the selected Mandelbrot cell
The values in the range E18:E200 are complex numbers, but Excel displays them as a text string and stores the complex number components in the background.
E19: calculates the sum of the power of the previous number and adds it to the original number using the formula:
E19: =IMSUM( IMPOWER(E18, $G$7), $E$18)
This is using IMSUM() to add the result of the squaring of the previous complex number E18 and the starting value, also in E18.
The squaring is done using IMPOWER(E18,$G$7), where E18 is the number being squared and $G$7 has the value 2 in it
E20: similarily has
E20: =IMSUM( IMPOWER(E19, $G$7), $E$18)
This is using IMSUM() to add the result of the squaring of the previous complex number E19 IMPOWER(E19, $G$7) and the starting value, in E18.
This is repeated below for the iterations
The Rsq value is calculated using the formula in F18
F18: Checks that the Absolute value of the complex number is not greater than the Iteration Escape value using the function
F18: =IF(IMABS(E18) > $G$9, 0, IMABS(E18))
In this case we can use the complex function IMABS() to calculate the Rsq value and check that against our escape iteration limit from Cell $G$9.
The rest of the calculations and function of the Mandelbrot are the same as the original version described above.
The final result is a Mandelbrot Chart shown:
You can manually zoom in on areas of the Mandelbrot by changing the starting coordinates
It isn’t difficult to see that this can be updated by using a simple macro and the Double Click event, where a user can double click on a cell within the Mandelbrot, and the macro transfers these coordinates into the cells above using a 10:1 or other predefined zoom factor
I hope you have enjoyed this introduction to iterative functions within the Excel worksheet and a foray into complex numbers
I have demonstrated that Excel can compute and display a Mandelbrot or in fact any iterative function without the use of Visual Basic on the Excel worksheet.
Excel has tools that allow multi-dimensional iterations within the worksheet space. This opens up the use of Excel for complex financial and other modelling analysis in multiple dimensions.
Excel can do all this using Real or Complex numbers.
Have you used complex numbers or iterative functions in Excel ? Let us know in the comments below.
The post The Excel Mandelbrot appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.
