Background

I have always had a fascination with Mandelbrots, since I was introduced to them in High School in the late 1970’s. With the advent of personnel computers, I could then program my own Mandelbrot plots in Quick Basic, before some of the highly optimised Fractal Programs popped up in the mid 80’s, my favorite at the time was Fractint.

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 !

That night over dinner, I had a light bulb moment and 20 minutes later the proof of how to develop a Mandelbrot in Excel was defined and working. I was recognized for that effort by John Walkenbach in his 2007 Excel Bible.

What is a Mandelbrot?

A Mandelbrot is a contour plot of the count of the escape iterations of the formula Z²=Z²+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 Z²=Z²+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 Z^_new2=Z^_old2+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.

My first post at Chandoo.org in 2009 was a discussion of how to use the Excel Data Table function which included a section on Iterative Functions and Fractals. https://chandoo.org/wp/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/.

Calculating a Mandelbrot

The Mandelbrot is the calculation of a solution to the equation, Z²=Z²+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 read about the math required in the Computer Drawings section of https://en.wikipedia.org/wiki/Mandelbrot_set

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 = Xold² – Yold² +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( Xnew² + Ynew² )

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.

Plotting the Mandelbrot

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.

Complex Math Mandelbrot

The Mandelbrot calculated and displayed above conforms to the maths described in the Computer drawings section of the https://en.wikipedia.org/wiki/Mandelbrot_set post. But I had always wondered whether Excel could calculate the Mandelbrot directly using complex maths.

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

https://best-excel-tutorial.com/59-tips-and-tricks/213-complex-numbers

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:

Zooming

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

Conclusion

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.

Written by Hui... Tags: Complex Numbers, Iteration, mandelbrot, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question