Excel Lissajous Curve Explorer

My posts often contain charts where the data for the series is generated on the fly, but not in worksheet cells. Rather they are generated from named formulas. The technique deserves some explanation.

LissajousCurves_image.png
I put together a Lissajous Curve Explorer and Gallery to demonstrate how this works. Lissajous curves make a good subject matter for this demonstration because minute changes in their parameterized variables can produce an infinite variation as the output.
Each plot in the Explorer contains 4,000 data points. Using named formulas to generate the data saves us the hassle and tedium of maintaining 8,000 cells of chart data (4,000 for x and 4,000 for y). Many of my projects contain dozens of series for a chart. In fact the Optical Illusions #4 had nearly the maximum of 255 series in the chart. So you can see how the number of cells to drive a chart could really add up. This technique avoids all of that.
What I do is create a named formula, n, to represent the number of points I want (this can be a constant or a formula).
The next step is to create a named formula, t:
=(ROW(INDIRECT("1!1:"&n+1))-1)*2*PI()/n
In the Lissajous Curve Explorer, we are going to feed the trig function SIN so our t needs to vary between zero and 2π. This produces, in this case, 4,000 equally spaced values for t.  Using ROW/INDIRECT is the same technique that we use in normal array formulas in worksheets to produce a loop – remember that all named formulas are in fact array formulas.
Next we define the names, a and b, and link them to two worksheet cells so our users can enter values for a and b.
Next we define the named constant, δ, as π/2; and set _A and _B both equal to one.
The last thing is to create our named formulas for x and y:
 x

=_A*SIN(a*t+δ)
and
y

=_B*SIN(b*t)
That’s it.  All we need to do now is create an XY (Scatter) chart with smooth lines and define a series:
Series name: =”Curve”
Series X values: =1!x
Series Y values: =1!y
Now we can plot an infinite number of Lissajous Curves using essentially no worksheet cells whatsoever. As part of the Excel Lissajous Curve Explorer I have included a gallery that you can scroll through to see a couple hundred of the best variations (in my opinion) that I’ve discovered thus far. You can edit the gallery by supplying values for a and b over in columns AB and AC on the worksheet.
I’ve provided a scroll control to allow for easy navigation through the gallery. You can also toggle to User Select for direct input of a and b values. There are scroll controls there as well.
If you find a Lissajous curve that you really like, tell me the values of a and b in the comments and I’ll try to add a web gallery to this post!
Here’s the workbook:
Reblog this post [with Zemanta]

Excel Optical Illusions #6

The Excel Optical Illusion this week looks easy, but it takes more work than what one might think:

optical_illusion_cube.png

Do you see a room with a cube in the corner? Or do you see a cube with its bottom-front corner missing? How fast can you switch back and forth?
You might think this can be done with a 3d chart. Nope.
To make this I used the same technique of layering series in an Area chart that I used to make the 42 flags in the Eurovision 2009 project and the American flag in the Flag Project.
All of the data for this chart is held as array constants within the chart series, and so no worksheet cells or named formulas are used.
I’ll have another Excel based optical illusion next Friday.
The file should work in all versions of Excel.
Here’s the workbook:
Reblog this post [with Zemanta]

Animated Stacked Chart

Today I had a couple of hours to kill and stumbled upon an interesting flash chart at flowingdata.com.

It’s an animated chart that allows you to explore Average Consumer Spending in the USA by category from 1984 through 2008. I thought it would be interesting to emulate the animation in Excel and here’s what I came up with:
AverageConsumerSpending.png
Now mind you, I’m not sure I like the value axis changing scale on a chart, but to emulate this properly that was a requirement. It actually turned out pretty well. If you click on any of the categories, the whole chart morphs… it’s pretty cool. Clicking again brings it back to normal. There’s a check box at the top to turn off the animation, and I suggest you do so if you plan to edit the chart.
This is most definitely a VBA project, but interestingly most of the work is done by worksheet formulas and the VBA glues it together and animates it. One thing I learned crafting this was that adding a shadow to the Chart Area dramatically crippled the speed at which the animation takes place – so I removed the shadow!
The project uses a modified version of Jon Peltier’s chart event class. You can re-color the chart categories by changing a couple of RGB values in the SetColor sub-procedure. The code there does a straight linear interpolation in RGB color space between the two provided colors for the 14 spending categories.
I think it would be time well spent to investigate how this chart works.
Here’s the workbook:

Excel, a Presentation Platform?

Presentation authoring is dead simple in PowerPoint. It definitely has its merits. But have you ever noticed that most presentations are similar, and frankly boring.

Ever wondered if Excel itself could be used to make a graphical presentation?
numberspiral_opening_page.png


I decided to make this presentation to test the idea. It uses one chart and one text box for the entire show. The slides are advanced with a scroll control and thus no VBA is required or used.
I can tell you that its not as easy as PowerPoint and there are no fancy transitions (most of which are worthless, in my opinion).
But there are some advantages as well.
The presentation can be more interactive. Excel has much better charting facilities. No kludgey OLE linking needed for dynamic charts. And in the end it’s not that difficult, especially if you follow the strategies promoted here at excelhero.com.
Each slide is drawn from values in a state machine using named formulas for the heavy lifting.
I chose to make the presentation about Robert Sacks’s Number Spiral, a fascinating topic to be sure, with some very interesting math. Robert was most kind in allowing me to do so.
For an enlightening experience, please download the workbook and let me know what you think!
— UPDATE —
Now you can watch a slide show if you don’t have Excel 2007.
Enhanced by Zemanta

Excel Optical Illusions #5

This week’s Excel optical illusion really bends my eyes!

optical_illusions_number_five.png


The illusion is caused by the pink and white stars. The attached workbook has check boxes that let you remove either of these and the illusion vanishes. Very interesting how such a strong illusion comes from such a simple chart.
The point of the optical illusions series is to open our eyes with the goal of making better charts. Am I succeeding in your opinion?
I’ll have another next Friday?
Here’s the chart:
More Optical Illusions
Enhanced by Zemanta

Eurovision 2009

Inspired by Chandoo’s Flag Project, I decided to add plotted flags to another bezier curve project I had in the works.

eurovision_image.png
The attached workbook showcases the results of the Eurovision 2009 Song Contest, using an XY (Scatter) chart and a VBA class to add events to that chart so the user just needs to move the mouse over a country’s flag icon to see the votes they received.
The bezier curves showing which countries voted for the selected country are drawn from named formulas that update based on which country is selected. I think it’s pretty cool.
So there were 42 participant countries and this workbook plots all 42 of those European flags in one chart just below the bezier chart. No VBA is used to plot these flags, just formulas. The VBA is used only for selecting countries, by hovering the mouse over their flag icon.
I like the minimal interface that the VBA makes possible, but you must make sure that the top chart is selected in order for it to work. The workbook open event does this for you, but if you select anything else you’ll have to click back on the top chart to get it working again.
Please let me know what you think.

Here’s the workbook:
Reblog this post [with Zemanta]

St. Patrick’s Day

I had a delightful surprise today when my 10-year old daughter made this 4-leaf clover for me in Excel:

4_leaf_clover.png
I told her she should turn it in for extra credit in math. I think she did a great job and I hope her teacher thinks so as well.
It’s just one data series in an XY (Scatter) chart.
The file works in all versions of Excel, but looks better in 2007 or later. Here’s the workbook if you’d like to see how she did it:
By the way, I hope you are wearing green today!
Reblog this post [with Zemanta]

Bezier Curve Chart

Many charts in Excel are visually boring.

Here is a chart that inspired me. It is showcasing Benford’s Law which says that many data sources that occur naturally, such as the size of the world’s countries exhibit an unexpected pattern in that the first digit is the numeral 1 about 30% of the time.
benfords_law_1.png
Considering that there are nine numerals, you would expect each numeral to occur about 11% of the time as the first digit in these numbers. But many natural data sources follow Benfords Law almost exactly.
Personally, I loved the look and feel of this chart (it’s really a poster) with the ordered chaos of color and bezier curves.
Please let me know what you think of it!
Here’s the workbook:
And here’s a 24 x 48 inch poster of the chart:
 Benford'sLawPoster_excelhero.com
Reblog this post [with Zemanta]

Excel Optical Illusions #4

This week’s optical illusion was a little more challenging, but I think it came out just fine.

optical_illusions_frasers_spiral.png
The rope is not a spiral, but rather a series of circles. You can check a box that spoils the illusion with a mask.
There’s some pretty cool Excel charting going on here. All of the swaths are drawn from formulas. Have fun tearing it apart!
I’ll have another next Friday…
Here’s the workbook:
Reblog this post [with Zemanta]

Monte Carlo PI

Ever wondered what a quarter of a million points might look like on an Excel chart?

MonteCarloPI.png
I thought this was a nice visualization of the Monte Carlo method, so I decided to share it. It uses over 2 million formulas, so I used VBA to create the formulas when you open the workbook, which takes about 10 seconds on my machine, but saves over 50 MB of file size!
Try varying the number of darts and see how the accuracy of PI is affected. This shows quite clearly that high precision results with the Monte Carlo method require very large samples. Of course an infinite sample size would produce a perfect value for PI… whatever that means ;)
Or try hitting F9 and have the worksheet re-calc.
An interesting display happens when you have the number of darts set to the max (264,000) and you ALT-TAB to another window that completely covers the chart and then ALT-TAB back! Each flash is 32,000 points being drawn on the chart.
Here’s the workbook:
Reblog this post [with Zemanta]