Excel Optical Illusions Week #28

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.
mutually_interfering_shapes_optical_illusion_excelhero.com.gifHere 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:
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – 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)

Enhanced by Zemanta

Excel High Precision Engineering Chart #1

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.


Here is a sliver of my version of the chart as an animated GIF.
smith_chart_excelhero.com.gif
As you can see it is large.
excel-hero-academy-web-banner1.jpg
Here is the complete chart.
smith_chart_small_size_excelhero.com.png
Click for larger version.
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:
smith_chart_full_circles_excelhero.com.png
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.
angular_circle_intersection.png
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!
Enhanced by Zemanta

Excel Optical Illusions Week #27

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.
swimming_fish_optical_illusion_excelhero.com.gif
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:
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – 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)
Enhanced by Zemanta

Excel Optical Illusions Week #26

In a couple of days I will be starting a new series of high precision engineering charts, starting with the publication of the compelling Smith Chart.
In the meantime, this week’s Excel Optical Illusion is called the Café Wall Illusion.

cafe_wall_optical_illusion_hui_excelhero.com.png
Are the tiles and horizontal lines parallel?
The Excel version of this illusion was crafted by Excel Hero reader, Hui. Another fantastic job, Hui. I also received a version of the Café Wall from Tim McCollough, but I’ve lost it. Tim if you send it to me again, I’ll post it here!
Hui’s version is a 100% Stacked Bar chart. Very cool. It’s a dynamic chart with a slider control that allows you to adjust the tiles. When they are aligned vertically, the illusion disappears completely.
Here is the file.
— UPDATE —
Tim McCollough was kind enough to resend his version of the Café Wall Illusion – also a very fine implementation.
Here is Tim’s 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:
Enhanced by Zemanta

Excel Optical Illusions Week #25

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

stepping_feet_radial_optical_illusion_excelhero.com.gif
As Michale Bach describes this illusion on his page that inspired me…
… Observe the move ment of blue and yellow patch es (‘feet’). Whenever the [radial] grid is visible, the feet seem to step out of phase, while in reality their movement is always par al­lel. Like tiny feet going tip-tap-tip-tap… The effect is more pronounced if one does not look directly on to the feet.
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:
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – 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)