How To Draw The Cantor Set In Google Sheets

The Cantor set is a special set of numbers lying between 0 and 1, with some fascinating properties.

It’s created by removing the middle third of a line segment and repeating ad infinitum with the remaining segments, as shown in this gif of the first 7 iterations:

Cantor Set Iterations

The formulas used to create the data for the Cantor set in Google Sheets are interesting, so it’s worth exploring for that reason alone, even if you’re not interested in the underlying mathematical concepts.

But let’s begin by understanding the set in more detail…

What Is The Cantor Set?

Cantor Set in Google Sheets

The Cantor set was discovered in 1874 by Henry John Stephen Smith and subsequently named after German mathematician Georg Cantor.

The construction shown in this post is called the Cantor ternary set, built by removing the middle third of a line segment and repeating ad infinitum with the remaining segments.

It is sometimes known as Cantor dust on account of the dust of points that remain after repeatedly removing the middle thirds. (Cantor dust also refers to the multi-dimensional version of the Cantor set.)

The set has some fascinating, counter-intuitive properties:

  • It is uncountable. That is, there are as many points left behind as there were to begin with.
  • It’s self-similar, meaning each subset looks like the whole set.
  • It’s fractal with a dimension that is not an integer.
  • It has an infinite number of points but a total length of 0.

Wow!

How To Draw The Cantor Set In Google Sheets

To be clear, the Cantor set is the set of numbers that remain after removing the middle third an infinite number of times. That’s hard to comprehend, let alone do in a Google Sheet 😉

But we can create a picture representation of the Cantor set by repeating the algorithm ten times, as shown in this tutorial:

Create The Data

Step 1:

In a blank sheet called “Data”, type the number “1” into cell A1.

Step 2:

In cell B1, type this formula:

={ FILTER(A1:A,A1:A<>"") ;
SUM(FILTER(A1:A,A1:A<>"")) ;
FILTER(A1:A,A1:A<>"") }

Step 3:

Drag this across your sheet up to column J, which creates the data for the first 10 iterations.

Each formula references the column to the left. For example, the formula in cell D will reference column C.

Your data will look like this:

Data For Cantor Dust in Google Sheets

How does this formula work?

It combines array literals and the FILTER function.

Let’s break it down, using the onion framework.

The innermost formula is:

=FILTER(A1:A,A1:A<>"")

This formula grabs all the data from column A and returns any non-blank entries, in this case just the value “1”.

Now we combine two of these together with array literals:

={ FILTER(A1:A,A1:A<>"") ;
FILTER(A1:A,A1:A<>"") }

Here the array literals { ... ; ... } stack these two ranges.

In this first example, it puts the number “1” with another “1” beneath it in column B.

Then we add a third FILTER and also SUM the middle FILTER range to create our final Cantor set algorithm:

={ FILTER(A1:A,A1:A<>"") ;
SUM(FILTER(A1:A,A1:A<>"")) ;
FILTER(A1:A,A1:A<>"") }

As we drag this formula to adjacent columns, the relative column references will change so that it always references the preceding column.

In column B, the output is:

1,1,1

Then in column C, we get:

1,1,1,3,1,1,1

And in column D:

1,1,1,3,1,1,1,9,1,1,1,3,1,1,1

etc.

This data is used in the sparkline to generate the correct gaps for the Cantor set.

Draw The Cantor Set

We’ll use sparklines to draw the Cantor set in Google Sheets.

Cantor Dust In Google Sheet
Click to enlarge

Step 4:

Create a new blank sheet and call it “Cantor Set”.

Step 5:

Next, create a label in column A to show what iteration we’re on.

Put this formula in cell A1 and copy down the column to row 10:

="Cantor Set "&ROW()

This creates a string, e.g. “Cantor Set 1”, where the number is equal to the row number we’re on.

Step 6:

The next step is to dynamically generate the range reference. As we drag our formula down column B, we want this formula to travel across the row in the “Data” tab to get the correct data for this iteration of the Cantor set.

Start by generating the row number for each row with this formula in cell B1 and copy down the column:

=ROW()

(I set up my sheet with the data in columns because it’s easier to create and read that way. But then I want the Cantor set in a column too, hence why I need to do this step.)

Step 7:

Use the row number to generate the corresponding column letter with this formula in cell C1 and copy down the column:

=ADDRESS(1,ROW(),4)

This uses the ADDRESS function to return the cell reference as a string.

Step 8:

Remove the row number with this formula in cell D1 and copy down the column:

=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Step 9:

Combine these two references to create an open-ended range reference for the correct column of data in the “Data” sheet.

Put this formula in cell E1 and copy down the column:

="'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

This returns range references e.g. 'Data'!A1:A

Step 10:

Put this formula in cell F1 and copy down the column:

=INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1",""))

This will show #REF! errors: “Array result was not expanded because it would overwrite data in…”

However, don’t worry, these are only temporary as we’ll dump this data into the sparkline formula next.

Step 11:

In column G, create a default sparkline formula:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")))

This shows the default line chart (except for the first row where it shows a #N/A error).

Step 12:

In column H, convert the line chart sparkline to a bar chart sparkline by specifying the charttype in custom options:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")),{"charttype","bar"})

Step 13 (optional):

Finally, in column I, change the colors to a simple black and white scheme, by specifying color1 and color2 inside the sparkline:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")),{"charttype","bar";"color1","black";"color2","white"})
Cantor Set Data Formulas
Click to enlarge

Feel free to delete any working columns once you have finished the formula showing the Cantor set.

Finished Cantor Set In Google Sheets

Here are the first 10 iterations of the algorithm to create the Cantor set:

Cantor Set In Google Sheet
Click to enlarge

Of course, this is a simplified representation of the Cantor set. It’s impossible to create the actual set in a Google Sheet since we can’t perform an infinite number of iterations.

Can I see an example worksheet?

Yes, here you go.

See Also

You might enjoy my other mathematical Google Sheet posts:

PI Function in Google Sheets And Other Fun π Facts

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

The FACT Function in Google Sheets (And Why A Shuffled Deck of Cards Is Unique)

The FACT Function in Google Sheets (And Why A Shuffled Deck of Cards Is Unique)

Let’s start with a mind-blowing fact, and then use the FACT function in Google Sheets to explain it.

Pick up a standard 52 card deck and give it a good shuffle.

The order of cards in a shuffled deck will be unique.

One that has likely never been seen before in the history of the universe and will likely never be seen again.

I’ll let that sink in.

Isn’t that mind-blowing?

Especially when you picture all the crazy casinos in Las Vegas.

Let’s understand why, and in the process learn about the FACT function and basic combinatorics (the study of counting in mathematics).

Four Card Deck

To keep things simple, suppose you only have 4 cards in your deck, the four aces.

You can create this deck in Google Sheets with the CHAR function:

CHAR cards

The formulas to create these four cards are:

Ace of Clubs:

=CHAR(127185)

Ace of Spades:

=CHAR(127137)

Ace of Hearts:

=CHAR(127153)

Ace of Diamonds:

=CHAR(127169)

Let’s see how many different combinations exist with just these four cards.

Pick one of them to start. You have a choice of four cards at this stage.

Once you’ve chosen the first one, you have three cards left, so there are 3 possible options for the second card choice.

When you’ve picked that second card, you have two cards left. So you have a choice of two for the third card.

The final card is the last remaining one.

So you have 4 choices * 3 choices * 2 choices * 1 choice = 4 * 3 * 2 * 1 = 24

There are 24 permutations (variations) with just 4 cards!

Visually, we can show this in our Google Sheet by displaying all the different combinations with the card images from above:

Fact function card combinations in Google Sheets

(I’ve just shown the first 6 rows for brevity.)

You can see for example, when moving from row 1 to row 2, we swapped the position of the two red suits: the Ace of Hearts and the Ace of Diamonds.

Five Card Deck

This time there are 5 choices for the first card, then 4, then 3, then 2, and finally 1.

So the number of permutations is 5 * 4 * 3 * 2 * 1 = 120

Already a lot more! I have not drawn this out in a Google Sheet and leave that as an optional exercise for you if you wish.

The FACT function

The FACT function in Google Sheets (see documentation) is a math function that returns the factorial of a given number. The factorial is the product of that number with all the numbers lower than it down to 1.

In other words, exactly what we’ve done above in the above calculations.

Four:

The 4 card deck formula is:

=FACT(4)

which gives an answer of 24 permutations.

Five:

The 5 card deck formula is:

=FACT(5)

which gives an answer of 120 permutations.

Six:

A 6 card deck is:

=FACT(6)

which gives an answer of 720 permutations.

Twelve:

A 12 card deck has  479,001,600 different ways of being shuffled:

=FACT(12)

(You’re more likely to win the Powerball lottery at 1 in 292 million odds than to get two matching shuffled decks of cards, even with just 12 cards!)

Fifty Two:

Keep going up to a full deck of 52 cards with the formula and it’s a staggeringly large number.

=FACT(52)

Type it into Google Sheets and you’ll see an answer of 8.07E+67, which is 8 followed by 67 zeros!

(This number notation is called scientific notation, where huge numbers are rounded to the first few digits multiplied by a 10 to the power of some number, 67 in this case.)

This answer is more than the number of stars in the universe (about 10 followed by 21 zeros).

Put another way if all 6 billion humans on earth began shuffling cards at 1 deck per minute every day of the year for millions of years, we still wouldn’t even be close to finding all possible combinations.

PI Function in Google Sheets And Other Fun π Facts

The PI function in Google Sheets returns the value of the mathematical constant pi (π) to 9 decimal places: 3.141592654

Pi is defined as the ratio of a circle’s circumference to its diameter.

Pi is denoted by the Greek lowercase letter π

PI Function in Google Sheets: Syntax

=PI()

There are no arguments. If you put anything between the brackets the formula will give you an error.

Circle Calculations With The PI Function

If you know the circumference of a circle then you can calculate the diameter and vice versa.

How to calculate the circumference

Suppose you have a diameter of 10 (the units are irrelevant).

Then you calculate the circumference with this formula: the diameter multiplied by pi.

= 10 * PI()

How to calculate the diameter

Now suppose you know the circumference is value 10.

The diameter is calculated as the circumference divided by pi:

= 10 / PI()

How to calculate the area of a circle

The formula for the area of a circle is π * r² (pi times the radius squared).

To calculate the radius, you must divide the diameter by 2.

So, knowing the diameter d, you can calculate the area of a circle as follows: π * (d/2)²

Taking a diameter of 10, the area is:

= PI() * ( 10 / 2 )^2

for an answer 78.5398

Creating the PI Symbol with the CHAR Function

The amazing CHAR function, which converts numbers into characters per the Unicode table, can output the symbol for pi in your Google Sheet.

Use this formula to output the pi symbol π:

=CHAR(960)

Setting the cell to the Merriweather font gives it the nice rounded appearance shown in this image:

Pi Symbol With Char Function

By the way, this formula:

=CHAR(129383)

will output an image of an entirely different type of pie in your Google Sheet:

Pie in a Google Sheet

PI function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Approximating Pi in Google Sheets With the Nilakantha Series

The Nilakantha series, named after the 15th century mathematician of the same name, is an infinite series that converges on pi as the number of iterations rises. It converges quickly so it’s a good algorithm to use to compute the digits of pi.

The Nilakantha series is:

Nilakantha Series
Image source: Wikipedia

Let’s build an approximation of pi in a Google Sheet using this series.

Nilakantha Series in Google Sheets With The PI Function

In cell A1, enter this SEQUENCE formula:

=SEQUENCE(1000,1,2,2)

And in B1:

=SEQUENCE(1000,1,3,2)

Then in C1:

=SEQUENCE(1000,1,4,2)

These three formulas give us three columns of numbers with 1,000 digits each. They start with 2, 3 and 4 respectively and increment by 2.

Look closely at the infinite series definition above and you’ll notice that each row represents a denominator for each iteration of the series.

In cell D1, multiply the values together for each row:

=PRODUCT(A1:C1)

Copy this formula down the column.

This formula generates the denominators for the series.

In cell E1, put this product as the denominator to calculate the series values:

= 4 / D1

Next, we need to alternate adding and subtracting these values, which we can do with this formula:

=IF( ISODD( ROW() ), E1 , UMINUS( E1 ) )

This IF formula checks if we’re on an odd numbered row (e.g. row 1) using the ISODD function and, if we are, returns the number unchanged. But if we’re on an even numbered row, it returns the negative value from column E, using the UMINUS function.

Finally we can approximate pi in column G.

In G1, put this formula to start the series:

= 3 + F1

In cell G2, put this formula:

= G1 + F2

and drag this formula all the way to the bottom of the range.

With 1,000 rows, we get a value for pi of 3.1415926533, which is a similar level of accuracy to the PI function.

Here are the first 16 iterations, the remaining rows up to 1,000 can be found in the template linked above in this article:

Nilakantha Series to approximate pi in Google Sheets

Now, you might be wondering how many digits of pi have been calculated…

…well it’s a whopping 31.4 trillion digits!

Obviously, that is not a calculation you can perform in your Google Sheet, but interestingly, it was performed on Google Cloud infrastructure!

Update: The record is now 50 trillion digits, broken by an enthusiast with a self-built server setup. Super impressive!

Read more about the chronology of computing digits of pi.

Getting More Digits of Pi in Google Sheets

Whilst we can’t get 50 trillion digits of pi into a Google Sheet, we can call the Pi Delivery API and retrieve more digits.

Here’s the code to request the first 1,000 digits of pi and display them in a Google Sheet as a text string (because numbers get truncated):

/** 
 * function to call the Pi API
 * https://pi.delivery/#apipi_get
 */
function getPI() {
  
  // endpoint
  const root = 'https://api.pi.delivery/v1/pi';
  
  // start point and number of digits
  const startDigit = 0;
  const numDigits = 1000; // max request is 1000 digits in 1 call

  // create endpoint
  const endpoint = root + '?start=' + startDigit + '&numberOfDigits=' + numDigits;

  // call the PI API to fetch digits of pi
  const response = UrlFetchApp.fetch(endpoint);
  const result = JSON.parse(response.getContentText());
  
  // get pi
  // keep pi as a string otherwise it gets truncated if a number
  // add single tick in front of the 3 to avoid automatic conversion to number in Sheet
  const piDigits = result.content;
  const pi = "'3." + piDigits.substring(1); 

  // paste result into Google Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const piSheet = ss.getSheetByName('PI API');
  piSheet.getRange(3,1).setValue(pi);
  
}

The API is limited to calls of up to 1,000 digits at a time, so if you want more than that you’ll need to expand the script above to request batches of 1,000.

More Resources For Mathematics in Google Sheets

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

How to draw the Mandelbrot Set in Google Shetes

This article will walk you through how to draw the MandelBrot set in Google Sheets, using only formulas and the built-in chart tool.

What Is The MandelBrot Set?

The Mandelbrot set is group of special numbers with some astonishing properties.

You’ve almost certainly seen an image of the Mandelbrot set before.

It’s one of the most famous mathematical concepts, known outside of mathematical circles even. It’s named after the French mathematician, Benoit Mandelbrot, who was the father of fractal geometry.

The Mandelbrot set is fantastically beautiful. It’s an exquisite work of art, generated by a simple equation.

More formally, the Mandelbrot set is the set of complex numbers c, for which the equation z² + c does not diverge when iterated from z = 0.

Gosh, what does that mean?

Well, it’s easier if you look at the picture at the top of this article. The black area represents points that do not run away to infinity when you keep applying the equation z² + c.

Consider c = -1.

It repeats -1, 0, -1, 0, -1… forever, so it never escapes. It’s bounded so it belongs in the Mandelbrot set.

Now consider c = 1.

Starting with z = 0, the first iteration is 1.

The second iteration is 1² + 1 = 2.

The third iteration is 2² + 1 = 5.

The fourth iteration is 5² + 1 = 26.

And so on 26, 677, 458330, 210066388901, … It blows up!

It diverges to infinity, so it is not in the Mandelbrot set.

Before we can draw the Mandelbrot set, we need to think about complex numbers.

Imaginary And Complex Numbers

It’s impossible to draw the Mandelbrot set without a basic understanding of complex numbers.

If you’re new to complex numbers, have a read of this primer article first: Complex Numbers in Google Sheets

It explains what complex numbers are and how you use them in Google Sheets.

To recap, complex numbers are numbers in the form

a + bi

where i is the square root of -1.

We can plot them as coordinates on a 2-dimensional plane, where the real coefficient “a” is the x-axis and the imaginary coefficient “b” is on the y-axis.

Use A Scatter Plot To Draw The Mandelbrot Set

Taking each point on this 2-d plane in turn, we test it to see if it belongs to the Mandelbrot set. If it does belong, it gets one color. If it doesn’t belong, it gets a different color.

This scatter plot chart of colored points is an approximate view of the Mandelbrot set.

As we increase the number of points plotted and the number of iterations we get successively more detailed views of the Mandelbrot set. A point that may still be < 2 on iteration 3 may be clearly outside that boundary by iteration 5, 7 or 10. Mandelbrot set with more iterations and points

You can see the outline resembles the Mandelbrot set much more clearly at higher iterations.

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

Here’s a simple approximation of the Mandelbrot set drawn in Google Sheets:

Simple Mandelbrot Set

Let’s run through the steps to create it.

It’s a scatter plot with 289 points (17 by 17 points).

Each point is colored to show whether it’s in the Mandlebrot set or not, after 3 iterations.

Black points represent complex numbers, which are just coordinates (a,b) remember, whose sizes are still less than or equal to 2 after 3 iterations. So we’re including them in our Mandelbrot set.

Light blue points represent complex numbers whose sizes have grown larger than 2 and are not in our Mandelbrot set. In other words, they’re diverging.

Three iterations is not very many, which is why this chart is a very crude approximation of the Mandelbrot set. Some of these black points will eventually diverge on higher iterations.

And obviously we need more points so we can fill in the gaps between the points and test those complex numbers.

But it’s a start.

Generating The Complex Number Coordinates

In column A, we need the sequence going from 0 to 2 and repeating { 0, 0.25, 0.5, 0.75, 1, 1.25, 1.5, 1.75, 2, 0, 0.25, 0.5, …}

In column B, we need the sequence 0, then 0.25, then 0.5 repeating 9 times each until we reach 2 { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.25, 0.25, 0.25, …}

This gives us the combinations of x and y coordinates for our complex numbers c that we’ll test to see if they lie in the set or not.

It’s easier to see in the actual Sheet:

Mandelbrot set data

We keep going until we reach number 2 in column B. We have 324 rows of data. There are some repeated rows (like 0,0) but that doesn’t matter for now.

Columns A and B now contain the coordinates for our grid of complex numbers. Now we need to test each one to see if it belongs in the Mandelbrot set.

Formulas For The Mandelbrot Algorithm

We create the algorithm steps in columns C through J.

In cell C2, we create the complex number by combining the real and imaginary coefficients:

=COMPLEX(A2,B2)

Cell D2 contains the first iteration of the algorithm with z = 0, so the result is equal to C, the complex number in cell C2, hence:

=C2

The second iteration is in cell E2. The equation this time is z² + c, where z is the value in cell D2 and C is the value from C2:

=IMSUM(IMPOWER(D2,2),$C2)

It’s the same z² + c in F2, where z in this case is from E2 (the previous iteration):

=IMSUM(IMPOWER(E2,2),$C2)

Notice the “$” sign in front of the C in both formulas in columns E and F. This is to lock our formula back to the C value for our Mandelbrot equation.

In cell G2:

=IMABS(D2)

Drag this formula across cells H2 and I2 also.

In cell J2, use the IFERROR function:

=IFERROR(IF(I2<=2,1,2),2)

Then leave a couple of blank columns, before putting the chart data in columns M, N and O, with the following formulas representing the x values and the two y series with different colors:

=A2

=IF(J2=1,B2,"")

=IF(J2=2,B2,"")

Thus, our dataset now looks like this (click to enlarge):

Mandelbrot dataset in Google Sheets

The final task is to drag that first row of formulas down to the bottom of the dataset, so that every row is filled in with the formulas (click to enlarge):

Mandelbrot dataset in Google Sheets

Ok, now we're ready to draw some pretty pictures!

Drawing The Mandelbrot Set In Google Sheets

Highlight columns M, N and O in your dataset.

Click Insert > Chart.

It'll open to a weird looking default Column Chart, so change that to a Scatter chart under Setup > Chart type.

Change the series colors if you want to make the Mandelbrot set black and the non-Mandelbrot set some other color. I chose light blue.

And finally, resize your chart into a square shape, using the black grab handles on the borders of the chart object.

Boom! There it is in all it's glory:

Simple Mandelbrot Set

Note: you may need to manually set the min and max values of the horizontal and vertical axes to be -2 and 2 respectively in the Customize menu of the chart editor.

How To Draw A Better MandelBrot Set in Google Sheets

Generating Data Automatically

Generating those x-y coordinates manually is extremely tedious and not really practical beyond the simple example above.

Thankfully you can use the awesome SEQUENCE function in Google Sheets to help you out. This formula will generate the x-y coordinates for a 33 by 33 grid, which gives a more filled in image than the simple example above.

=ArrayFormula(UNIQUE({
MOD(SEQUENCE(289,1,0,1),17)/8,
ROUNDDOWN(SEQUENCE(289,1,0,1)/17)/8;
-MOD(SEQUENCE(289,1,0,1),17)/8,
ROUNDDOWN(SEQUENCE(289,1,0,1)/17)/8;
MOD(SEQUENCE(289,1,0,1),17)/8,
-ROUNDDOWN(SEQUENCE(289,1,0,1)/17)/8;
-MOD(SEQUENCE(289,1,0,1),17)/8,
-ROUNDDOWN(SEQUENCE(289,1,0,1)/17)/8
}))

Then drag the other formulas down your rows to complete the dataset, in the same way we did above.

Then you can highlight columns M, N and O to draw your chart again.

Note: you may need to manually set the min and max values of the horizontal and vertical axes to be -2 and 2 respectively in the Customize menu of the chart editor.

With three iterations, the chart looks like:

Mandelbrot set in Google Sheets with 3 iterations

Increasing The Iterations

Let's look at 5 iterations and 10 iterations, and you'll see how much detail this adds.

To move from 3 iterations to 5, we need to add some columns to our Sheet and repeat the algorithm two more times.

So, insert two blank columns between F and G. Label the headings 4 and 5.

Drag the the formula in F2 across the new blank columns G and H (this is the z² + c equation as a Google Sheet formula):

In G2:

=IMSUM(IMPOWER(F2,2),$C2)

In H2:

=IMSUM(IMPOWER(G2,2),$C2)

We need to add the corresponding size calculation columns. Between K and L, insert two new blank columns and drag the IMABS formula across.

Now in L2:

=IMABS(G2)

And in M2:

=IMABS(H2)

Finally, update the IF statement in column to ensure it's testing the value in column M now:

=IFERROR(IF(M2<=2,1,2),2)

And that's it!

Our chart should update and look like this:

Mandelbrot set in Google Sheets with 3 iterations

You can see the shape of the Mandelbrot set much more clearly now.

Increasing from 5 iterations to 10 iterations is exactly the same. Add 5 blank columns and populate with the formulas again.

The resulting 10 iteration chart is better again:

10 iterations

Increasing The Number Of Data Points

Increasing the number of points to 6,561 in an 81 by 81 grid will give a more "complete" picture than the examples above.

This sequence formula will generate these datapoints:

=ArrayFormula(UNIQUE({
MOD(SEQUENCE(1681,1,0,1),41)/20,
ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
-MOD(SEQUENCE(1681,1,0,1),41)/20,
ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
MOD(SEQUENCE(1681,1,0,1),41)/20,
-ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
-MOD(SEQUENCE(1681,1,0,1),41)/20,
-ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20
}))

Be warned, as you increase the number of formulas in your Sheet and the number of points to plot in your scatter chart, your Sheet will start to slow down!

Adding Color Bands

We can add color bands to show which iteration a given point "escaped" towards infinity.

For example, all of the points that are larger than our threshold on iteration 5 get a different color than those that are less than the threshold at iteration 5, but become larger on iteration 6.

The formulas for the iterations and size are the same as the examples above.

Then I determine if the point is still in the Mandelbrot set:

=IF(ISERROR(AG2),"No",IF(AG2<=2,"Yes","No"))

And then what iteration it "escapes" to infinity, or beyond the threshold of 2 in this example:

=IF(AH2="Yes",0,MATCH(2,S2:AG2,1))

These formulas are shown in the Google Sheets template:

Next we create the series for the chart:

First, the Mandelbrot set:

=IF(AH2="Yes",B2,"")

Then series 1 to 15:

=IF($AI2=AK$1,$B2,"")

The range for the scatter plot is then:

A1:A6562,AJ1:AY6562

where column A is the x-axis values and columns AJ to AY are the y-axis series.

Drawing the scatter plot and adjusting the series colors results in a pretty picture (this is an 81 by 81 grid):

15 iterations with color bands

Reaching Google Sheets Practical Limit

As a final exercise, I increased the plot size to 40,401 representing a grid of 201 by 201 points. This really slowed the sheet down and took about half an hour to render the scatter plot, so not something I'd recommend.

The picture is very pretty though!

How to draw the Mandelbrot Set in Google Shetes

The 40,401 x-y coordinates can be generated with this array formula:

=ArrayFormula(UNIQUE({
MOD(SEQUENCE(10201,1,0,1),101)/50,
ROUNDDOWN(SEQUENCE(10201,1,0,1)/101)/50;
-MOD(SEQUENCE(10201,1,0,1),101)/50,
ROUNDDOWN(SEQUENCE(10201,1,0,1)/101)/50;
MOD(SEQUENCE(10201,1,0,1),101)/50,
-ROUNDDOWN(SEQUENCE(10201,1,0,1)/101)/50;
-MOD(SEQUENCE(10201,1,0,1),101)/50,
-ROUNDDOWN(SEQUENCE(10201,1,0,1)/101)/50
}))

Zooming In On The Mandelbrot Set

Mandelbrot set zoomed in

Mandelbrot sets have the property of self-similarity.

That is, we can zoom in on any section of the chart and see the same fractal geometry playing out on infinitely smaller scales. This is but one of the astonishing properties of the Mandelbrot set.

Google Sheets is definitely not the best tool for exploring the Mandelbrot set at increasing resolution. It's too slow to render graphically and too manual to make the changes to the formulas and axis bounds.

However, as the maxim says: the best tool is the one you have to hand.

So, if you want to explore in Google Sheets it is possible:

Generating Zoomed Data

I'm going to zoom in on the point: -0.17033700000, -1.06506000000

(Thanks to this article, The Mandelbrot at a Glance by Paul Bourke, which highlighted some interesting points to explore.)

Starting with this formula in cell A2 to generate the 6,561 data points (I wouldn't recommend going above this because it becomes too slow):

=ArrayFormula(UNIQUE({
MOD(SEQUENCE(1681,1,0,1),41)/20,
ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
-MOD(SEQUENCE(1681,1,0,1),41)/20,
ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
MOD(SEQUENCE(1681,1,0,1),41)/20,
-ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20;
-MOD(SEQUENCE(1681,1,0,1),41)/20,
-ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20
}))

In columns C and D, I transformed this data by change the 0,0 center to -0.17033700000, -1.06506000000 and then adding the values from A and B to C and D respectively, divided by 100 to zoom in.

=C$2+A3/100
=D$2+B3/100

The rest of the process is identical.

I set the chart axes min and max values to match the min and max values in each of column C (x axis) and D (y axis).

Mandelbrot set zoomed in

This looks continuous because the chart has a point size of 10px to make it look better.

If I reset that to 2px, you can see clearly that this is still a scatter plot:

Mandelbrot scatter plot

I hope you enjoyed that exploration of the Mandelbrot set in Google Sheets! Let me know your thoughts in the comments below.

Complex Numbers in Google Sheets

Complex numbers are numbers in the form a + bi, where i is the square root of -1.

What on earth does that mean?

Imaginary And Complex Numbers

To start, consider an integer, say the number 4. The square root of 4 is 2. Easy peasy.

Now consider -4. What’s the square root of that?

It’s not -2, because -2 * -2 = 4 (a minus multiplied by a minus is a positive in mathematics).

No real number will equal the square root of – 4, so we need a new number.

This new number is called an imaginary number (no, I didn’t just make that up!) and is denoted by i and defined as the square root of -1.

i = √-1

or put another way:

i² = -1

A complex number is a number that has real and imaginary parts, and can be expressed in the form:

a + bi

where a and b are real numbers and i is the imaginary number defined above.

We can look at complex numbers visually in a 2-dimensional plane, with the x-axis representing the real numbers and the y-axis representing the imaginary numbers:

Complex Number Plane

Complex numbers are usually represented by the letter z, and written:

z = a + bi

Let’s go and create one in Google Sheets!

Complex Numbers In Google Sheets

Let’s create a complex number in Google Sheets.

Starting in a blank Sheet, add the numbers 3 and 2 into cells A1 and B1 respectively.

In C1, add this formula with the COMPLEX function:

=COMPLEX(A1,B1,"i")

This is what our complex number looks like in Google Sheets:

Complex Number Function in Google Sheets

Size Of A Complex Number

We can calculate the size of a complex number, called its absolute value or modulus value and written |z|, by measuring how far it is from the center point (0,0).

We do this using the Pythagorean formula for the longest side of a right angle triangle, namely the square root of the squares of the other two sides.

Google Sheets has a handy function, called IMABS, that will calculate this value for us.

Put this formula into cell D1:

=IMABS(C1)

It gives the result 3.605551275.

Complex Number size in Google Sheets

This is the same as doing the square root of the squares of the other two sides:

=SQRT(3^2+2^2)

Answer 3.605551275.

Adding Complex Numbers

The next operation we need to know how to do with complex numbers is how to add them.

What’s 3 + 2i added to 5 + 7i?

To get the answer we add the real parts and imaginary parts separately, and combine into a new complex number.

So

z = (3 + 2i) + (2 + 7i)
= (3 + 2) + (2i + 7i)
= 5 + 9i

Back in our Google Sheet, I’ve added a new row for the new complex number and used the IMSUM function to add them together:

=IMSUM(C1:C2)

which looks like this in our Google Sheet:

Adding complex numbers in Google Sheets

Squaring Complex Numbers

Another operation we need to perform on our complex numbers is to square them.

The IMPOWER function does the trick. It returns a complex number raised to a power.

In our Google Sheet, use this formula in cell D1:

=IMPOWER(C1,2)

which gives the answer 5 + 12i, calculated as follows:

(3 + 2i)² = (3 + 2i) * (3 + 2i)
= 3 * 3 + 2i * 3 + 3 * 2i + 2i * 2i
= 9 + 6i + 6i + 4 * i²
= 9 + 12i + 4 * -1
= 9 - 4 + 12i
= 5 + 12i

Extracting Real And Imaginary Parts

You can use the IMREAL function to return the real coefficient of a complex number (the “a” part).

And IMAGINARY function returns the imaginary coefficient (the “b” part).

There are other, more specialized complex numbers functions in Google Sheets too, but they’re beyond the scope of this article.

Why Are Complex Numbers Useful?

For mathematicians, complex numbers are just as “real” as the real numbers.

They have applications in many areas of mathematics, physics and engineering.

One area where you may have seen them without realizing, is in fractal geometry and specifically pictures of a beautiful set of complex numbers called the Mandelbrot set:

How to draw a Mandelbrot Set in Google Shetes

Here’s How To Draw The Mandelbrot Set In Google Sheets.