OK. I need your help!

**. For such a simple visualization, the project had a surprising number of challenges. Here is my work thus far.**

*Our Aging World*

– Mutually Interfering Shapes (Optical Illusion)

OK. I need your help!

I can’t decide what is the best strategy to emulate a certain aspect of this visualization. But I’ll get to that in a minute. This chart is a replication of General Electric’s *Our Aging World*. For such a simple visualization, the project had a surprising number of challenges. Here is my work thus far.

As always, the Excel visualization is much better than this animated GIF.

This post is not about the merits of the original visualization, but rather how it was emulated using advanced Excel techniques.

I grabbed the data from the UN database. And of course it was only available in 5-year increments when broken down by gender and age bracket. So for this visualization I had to interpolate the interim years – and that is what accounts for most of the data on the data sheet. Next I had to turn each year’s gender-age-bracket data into a percentage of the total for that year so that all countries and years would have a comparable relative scale.

To speed up the animation I needed to replace these tens of thousands of formulas with hard data. The charts are based on four very small ranges of formulas in columns BA and BB of the data sheet. These use the INDEX function to grab the appropriate data for the selected countries and year.

The animation is the very same technique used in the first two animated business charts.

An interesting aspect of this visualization is the creation of the legend controls to select countries and the clickbar at the bottom to select years. These are built from shapes and images and are powered by VBA.

There are some very interesting VBA and named formula twists going on here, so have a look.

Each vertical bar in the chart represents an age group for that country. For example the first bar represents the relative number of people in a country (in the selected year) who are aged 0 through 4. The second bar represents people 5 though 9. This continues for each 5-year age bracket up until the last vertical bar, which represents 100+.

In GE’s original, they use the technique of mousing over the bars to show a pop-up that explains what each bar means.

I can do this, but it’s clunky for all of the reasons described in my Stremgraph post. So what should I do? One idea is to allow clicking on the bars which would update a static legend box – not great but workable. I can’t think of a stellar idea just now. If you have ideas, let me know, and if I really like one, I will update the chart here and give you credit for the idea.

Here is the workbook.

PS. Big announcement tomorrow. Stay tuned!

If you like my blog, please be sure to sign-up for the new Excel Hero Newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

This week’s Excel Optical Illusion is called The Mutually Interfering Shapes Illusion and was originally created by Maarten Wijntjes, Robert Volcic and Tomas Knapen. The illusion was a 2008 Finalist in the Best Illusion of the Year Contest hosted by the Vision Sciences Society.

Here is how they described the illusion for the contest…

A circle’s a circle and a square’s a square, right? Wrong! Just look at the center of our MISillusion display and you’ll see why – Two dots are moving around bouncing off one another. Do you see a square and a circle? They really are! The illusion becomes even stronger when tracing the inner square closely: you’ll see the outer dot moving along four arcs instead of one circle. If you now follow the outer dot, the square suddenly seems curved inward!

So watch out next time you track a baseball pitch, your brain might just throw you a curveball!

My Excel version of this illusion is much smoother than the animated GIF above can reproduce. Wow that’s starting to sound like a broken record, but it’s the truth.

This is an XY Scatter chart with a little VBA for the animation. I’m very happy how it turned out, especially the color fading happening simultaneously with the rest of the animation. Again, the workbook is much, much better than the GIF.

Here is the file.

If you like my blog, please be sure to sign-up for the new Excel Hero Newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Why do I share these optical illusions? The techniques that are used to make them, when mastered, can be used in many other Excel projects, in charting, formula crafting, and formatting. Learn them. They will aid you on your journey to become an Excel Hero.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

The first high precision engineering chart that I would like to share is called the Excel Smith Chart. It is used by radio frequency engineers to drastically simplify complex equations (imaginary numbers) for matching circuits and transmission lines. It is so effective that it has been used since the 1930’s and is still used today. Even some high powered engineering software packages use the Smith Chart as the user interface.

For an outstanding tutorial on Smith Charts and how to use them click here.

Here is a sliver of my version of the chart as an animated GIF.

As you can see it is large.

Here is the complete chart.

The images above were taken at Excel’s 200% zoom setting. There is so much information on the chart that the normal 100% zoom is insufficient. I have spent a lot of time with this chart at Excel’s maximum of 400%.

This release of the chart is dynamic in that you can hide or show the internal arcs. Selecting the Show Arcs Slow option allows you to see how the chart is composed. At first glance it may not be apparent, but all of the internal arcs are portions of larger perfect circles. This image may help you see that better:

Look at the arcs within the red circle and see how they are just segments of full circles extending upward and downward.

I will soon release an enhanced version that will allow for interactivity that an engineer would actually use, so this becomes a real tool. For now this is an exercise in very advanced Excel chart construction.

When given a choice, I prefer to keep mathematically derived data that is only used to draw a chart off of any worksheets. I prefer to keep this information as metadata in the form of named formulas in the Name Manager. Many of my projects shared here on Excel Hero are presented in this fashion. There is a downside however, due to a bug in Excel. There is no way to get a series to plot a gap (in a line, for example, resulting in two line segments with the gap in between) when the series is based on a named formula. When using a worksheet, a blank cell in a range does the trick. As a result, each line segment needs its own formula. My Smith Chart has 356 data series, and each data series requires two named formulas, one for the X values and one for the Y values. And of course there are other named formulas for support. Obviously I used VBA to produce them! Have a look at the subroutine, AddArcs().

The primary region of the chart is the circle containing the arcs. Notice that there are three sets of circles here. The first set is the circles that stay within the main circle and get smaller and smaller going to the right. This set represents the REAL number component of the chart.

The next two sets represent the IMAGINARY number components of the chart, and all of these circles are clipped by the main circle. The first set of these go up and away from the horizontal axis with circles getting smaller and smaller. The last set of circles goes down and away from the horizontal axis. The horizontal axis itself is the infinitely large IMAGINARY number circle that sits in between the ones going up and the ones going down.

The first challenge is to figure out the mathematical relationship for the size of the circles in all three sets. Well, actually just the first two because set three is just a mirror opposite of set two.

Early on I decided to make the main circle a Unit circle, which means that it has a radius of 1, and I placed it at the Cartesian coordinates of (0,0).

After a little fiddling, I determined that the formula for the size of the circles in set 1 (the REAL number circles) was:

=(1+n*0.01)^-1

where n is a possible circle number starting at the left, just inside the main circle.

Likewise I determined that the relationship for the size of the circles in set 2 (the IMAGINARY number circles going up) was:

=(10*n^-0.5)^2

Where n is a possible circle number starting just above the horizontal axis.

I say possible circle number because the chart can show many more arcs then what it does shows now. In fact they are infinite. My version of the chart shows only a small sampling of circles sizes (radii) that these formulas define.

So which circles to show? By visually emulating the Wikipedia example, I chose to include the first 20 radii from these formulas (n=1, n=2, n=3, …n=20). Following that I included every other number for the next 15 circles in each group (n=22, 2=24, n=26, …n=50). Then I included every fifth number for the next 10 circles (n=55, n=60, n=65, …n=100). Then every tenth number for the next 10 circles (n=110, n=120, n=130, …n=200). Then every twentieth number for the next 15 circles (n=220, n=240, n=260, …n=500). Then every hundredth number for the next 5 circles (n=600, n=700, n=800, …n=1000). Then every two-hundredth for the next 5 circles (n=1200, n=1400, …n=2000). And finally every thousandth for the final 3 circles (n=3000, n=4000, n=5000).

I used this sampling of n for both formulas, and hence all three sets of circles.

Drawing a circle with named formulas is simple enough:

For the X values:

=r*COS(t)

For the Y values:

=r*SIN(t)

where r is the radius and t is an array of equally spaced values from 0 to 2π. What you get is not really a circle but a polygon with the number of sides equal to the number of values in this array. But if you set the series to Smoothed Line, anything above 70 points will make a nice circle. I’m using 100 points for each circle in this chart.

To produce t from a name formula, I am using:

=(ROW(OFFSET(‘1’!$A$1,,,n))-1)*(2*PI()/(n-1))

where n = the number of points I want. Again, 100.

But for the most part, we are interested in arcs, not the full circles. This means that we need to know the starting point and extent (length) of each arc in radians.

I had hoped to find a general formulaic solution to the ANGULAR INTERSECTION PROBLEM depicted in the above graphic for any two intersecting circles so that all formulas could be named (and hidden), but alas the solution remains elusive (with several math gurus helping me). I came close with the law of cosines, but acute angles from the center of Circle B had unacceptable rounding errors – meaning the starting points of the arcs were not precisely at the intersection.

So I had to ferret these out manually by painstaking trial and observation and then record the starting and extent angles in worksheet cells. I am hoping that I will soon be able to find the formulaic way to do this and get rid of all of the worksheet clutter for the arcs. Notice that some of the tolerances required to hit the intersection points are at the 5th decimal place.

The formula for t above will start at radian 0, so all we need to do is add a value to each n inside of t to control the starting point angle. Similarly we just need to multiply the result by another value to control the extent. So the formula for t for the arcs becomes:

=(START+(ROW(OFFSET(‘1’!$A$1,,,n))-1)*(EXTENT*PI()/(n-1)))

And finally for the Y values for the Set 3 circles we just need to subtract π from the START value to get the mirror image.

Notice that with this strategy we can start an arc at any arbitrary angle around its parent circle, with any precision we like. This is the primary reason I chose an XY Scatter chart instead of a Radar chart.

Now how to chart 356 data series on a chart? Answer: you don’t. Here we are in 2010 and still have the silly limitation of 255 maximum series in a chart. Of course if Excel plotted named formula nulls properly, we could do all the arcs in just two series, one for the darker arcs and one for the lighter ones.

The only option is to move to two charts placed one over the other, the top one having a transparent background. Silly really. But it works. For the most part the lighter lines are on the bottom chart. All of the darker lines are on the top chart. There were more than 255 lighter lines – so a few joined the top chart.

Now there is no such thing as radial aligned text in Excel. Nor is there a radial axis. I constructed the radial axes from circles and lines. The radial labels are rotated individually.

I would like to thank Robert Mundigl of the outstanding Clearly and Simply blog for taking the time to meticulously help me track down and fix a VBA problem with European versions of Excel that are set to use a comma instead of a decimal point. Not an easy thing to work with when attempting to make a workbook work across versions. I may write a blog post on it. It’s absolutely crazy. Thank you very much Robert!

I would also like to thank Rafael Nicolas Fermin Cota for gallantly attempting to solve the angular intersection problem. I believe he soon will! Thanks Nico.

The file has been tested in Excel 2002/2003, 2007, and 2010. Saving it in .xls format causes it to triple in size, so here’s both versions.

*** UPDATE ***

I hope to update this chart very soon with interactivity from an engineering perspective. If you would like to be notified when I do, please sign-up for my newsletter!

BTW, if you have or know of an interesting high precision engineering chart that you would like to see included in this series, please let me know. It may be something that I will want to include as well!

I’m planning something very special for the Excel Hero Newsletter in the next couple of weeks, so please make sure you are subscribed!

As promised last week, the Smith Chart will be published in a few hours. It is one of the most demanding charts I have ever created, and it is just about ready. Please look for it Monday.

In the meantime, this week’s Excel Optical Illusion is called Swimming Fish, and was originally created by Emily Knight and Arthur Shapiro. It was a 2007 Finalist in the Best Illusion of the Year Contest hosted by the Vision Sciences Society.

Are the fish bobbing up and down? I can assure you that they do not move.

My Excel version of this illusion is much smoother than the animated GIF above can reproduce. It includes an option to remove the moving background, which eliminates the illusion.

This is an XY Scatter chart with a little VBA for the animation. Pretty straightforward but I like how the background lines were made to seem infinite when there are only six of them in actual fact.

Here is the file.

If you like my blog, please be sure to sign-up for the new Excel Hero Newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Why do I share these optical illusions? The techniques that are used to make them, when mastered, can be used in many other Excel projects, in charting, formula crafting, and formatting. Learn them. They will aid you on your journey to become an Excel Hero.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

This week’s Excel Optical Illusion is called the Stepping Feet Radial Illusion.

As Michale Bach describes this illusion on his page that inspired me…

This project started out as a chart, but I was not happy with the outcome so I changed it to animated shapes. The spokes image in the background was creates with a Radar chart. I left the named formulas for the series in the name manager if you would like to look at them.

The “feet” are shapes grouped together with an invisible line that stretches all the way across the circle, centered on the circle’s center. For the animation I use VBA to rotate this grouped shape.

At every 12% of the way around the circle, the VBA toggles the visibility of the radial grid. When the grid is not visible it is obvious that the feet never shift but are locked in parallel motion.

Here is the file.

If you like my blog, please be sure to sign-up for the new Excel Hero newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Why do I share these optical illusions? The techniques that are used to make them, when mastered, can be used in many other Excel projects, in charting, formula crafting, and formatting. Learn them. They will aid you on your journey to become an Excel Hero.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

This week’s Excel Optical Illusion is called the Reverse Spoke Illusion.

Which way are the spokes moving?

As Michale Bach describes this illusion in the his page that inspired me…

I think this is the first time in ten years I’ve used a pie chart! This illusion uses VBA code very similar to *butterfly rainbow to vary the colors of the chart.*

Here is the file.

If you like my blog, please be sure to sign-up for the new Excel Hero newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

This week’s Excel Optical Illusion is called The Freezing Rotation.

This animated GIF does not do justice – the workbook animation is much smoother.

As Michale Bach describes this illusion in the his page that inspired me…

My Excel version is a collection of shapes that are being animated by VBA, sporting easing. The VBA is dead simple so please download the file to see how it is done.

Here is the file.

If you like my blog, please be sure to sign-up for the new Excel Hero newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

FreshBooks is a fantastic online invoicing and time tracking service. I use it and love it. In just over the first four months of this year they reached $1 billion US in invoices run through their system, and to celebrate they published a very interesting chart. The chart was originally designed by interactivethings.com. I thought I would recreate it in Excel with a twist – animation.

It’s pretty faithful to the original, with the added ability to sort the chart by countries with the most or least average invoice amounts; or with the most or least average time-to-pay on the invoices; or simply alpha or reverse alpha. You do this by clicking the legend to the left of the chart, as demonstrated in the above animated GIF.

This is a very advanced Excel chart, sporting bezier curves, bubble chart overlay with VBA rendered proportional font sizing, combination stacked bar chart and XY scatter chart, all series fed by named formulas with the original data in a data sheet with no supporting (cluttering) columns, two-dimensional dynamic range as the foundation of virtually all formulas, essentially no use of the IF function, animation with easing, VBA rendered font color fading, data sorting via the Sort method of the Range object, and liberal use of shortcut range references in the VBA. This is an instructional goldmine so please be sure to download the workbook.

I think it all came together quite nicely.

I placed the map behind the transparent charts in order to avoid the speed penalty of using a graphic as the back fill of a chart. I used this map from wikimedia.com. I lightened it up in Adobe Illustrator and used a circle vector mask to blow-up Europe, as was done in the original. It’s the only way to separate the bubbles of Europe enough so that their labels don’t overlap.

So what are your thoughts on this bad boy?

Here’s the chart.

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

– Freezing Rotation (Optical Illusion)

– Reverse Spoke Illusion (Optical Illusion)

– Stepping Feet Radial Illusion (Optical Illusion)

– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

– Kaleidoscope Motion (Optical Illusion)

– Curry’s Paradox (Optical Illusion)

– The Haze Illusion (Optical Illusion)

– The Koffka Ring (Optical Illusion)

This week’s Excel Optical Illusion is called Perpetual Collisions. The original work by Arthur Shapiro and Emily Knight was a 2008 finalist at the Best Illusion of the Year Contest, held by the Vision Sciences Society.

Here is the description of the Perpetual Collisions Illusion directly from their entry:

My Excel version (pictured in the animated GIF above) seems to work well in Excel 2002, 2007, and 2010, although I had to add more formulas just to support Excel 2002.

The chart is an XY Scatter type using inserted Excel shapes as the markers. When using compound shapes as markers, it is very frustrating because Excel changes the shape ever so slightly when copied to the chart’s data series. I experienced the exact same frustration when I was crafting **celtic muse** and those tiles are much simpler than these. It took quite a bit of trial and error to get them working as well as they are. It’s not perfect, but it’s close enough! I’ve included the tiles below the chart if you want to have a whack at it.

All of the series are fed from named formulas. The formulas that have a name like **frame_[n]_y_coords** are there only because Excel 2002 required them in order to not display a frame’s markers when that frame was out of scope. So adding these names enabled the chart to work in all versions. Likewise all the **-100** values in the formulas are a requirement of Excel 2002.

The arrays for the series use two different techniques in their construction. There are four frames in the animation. The frame marker series array is constructed by multiplying together two constant arrays and then we just use INDEX to get the appropriate column for each frame. The diagonals series on the other hand is created by a completely different technique. Can you figure it out?

For such a simple display, this is a pretty advanced Excel chart and well worth your study time.

So what is your impression of the Perpetual Collisions Illusion chart?

Here is the file.

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)

– Two Sinusoids (Optical Illusion)

– Perpetual Collisions (Optical Illusion)

The Excel Optical Illusion this week is an interpretation of an award winning optical illusion. It is called Two Sinusoids. It captured 3rd place this year at the 6th Annual Illusion of the Year Contest held by the Vision Sciences Society.

A very interesting thing happened. As you know, Hui has submitted several works that I have published, and I thought I should let him know that I had already replicated this illusion in Excel, in case he was thinking of doing so. I sent him an email and he shot one straight back, saying he had done it too and was preparing to send it to me! Yikes.

As always, he did a fabulous job and so I will make both available for you to download. Here is a picture of mine:

There are six different ways for your brain to interpret this image. You can see what look a little like American footballs scrolling to the left. Look closer and you can see the whole thing turning toward you like an auger on its side. Keep looking and it will start to spin the other way.

There are three more interpretations, but I’ll leave them for you to explore with the downloads and the original.

Hui and I both used the concept of multiplying frequencies to produce our respective versions, though we implemented that idea in very different ways. Both are highly iterative charts with lots of options. You can adjust the two frequencies and produce an infinite number of harmonic patterns. In my version I put a couple hundred of these interesting patterns into a gallery and you can interactively display random patterns from the gallery. It’s very cool.

Both of our charts are of XY Scatter type. I used named formulas for the series. Hui used worksheet cells to do the calculations that feed his series. Both use VBA extensively.

If you are serious about learning advanced Excel charting, I believe that you can benefit by downloading both and taking the time to understand how they work.

Mine will work to a degree in older versions of Excel, but some of the interactivity will be disabled. I’ve tested it in Excel 2002, 2007, and 2010. It looks best on 2007, and for some mysterious reason, the animation is quicker in 2007 than 2010. From everything I’ve read, it is supposed to be the other way around. Since it is so fast in 2007, I added an option to include a border on the data point markers, which really slows it down. The animated GIF above was recorded with this setting on. It is much, much faster with it off.

Hui’s version is for 2007+ only.

Here are the workbooks.

Daniel’s version:

Hui’s version:

Here is a list of other Excel Optical Illusions here at Excel Hero:

- Enigma

And here’s a list of other animated charts on Excel Hero:

&

nbsp;- HTML 5 Readiness

nbsp;- HTML 5 Readiness

– Excel, A Presentation Platform (Number Spiral)

– Lilac Chaser (Optical Illusion)

– Stereokinetic (Optical Illusion)

– Illusory Contours (Optical Illusion)

– Breathing Square (Optical Illusion)

– Enigma (Optical Illusion)