March 2010 Archives

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:

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:

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:

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

 

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.

 

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:

- The Haze Illusion

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.

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!

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.

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:

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:

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:

He's basically asked his blog readers to make Excel charts to depict their countries' flags and to submit them to the Flag Project.

I couldn't resist. Here's an image of my submission:

The chart is based on the official USA flag specifications and uses those specs in the chart series formulas.

Here's the workbook:

These are not "flow charts." Instead they are "energy flow" diagrams. They tell a rich story regarding how much energy the USA uses; in what form; and for what purpose. I don't particularly like their super saturated colors, but the information is great. From what I understand, the lab puts an enormous number of resources into gathering the data and producing these charts.

I decided to replicate the chart in Excel 2007. Here's an image of out it turned out:

This was seriously tedious work, as Excel has no native chart type to do what is required automatically. Each flow pipe is proportional in it's thickness to how much energy it represents. The thickness of a line can be easily adjusted in a chart dialog, but not by formula. Another difficulty is that a number of flow pipes need to stack on top of each other to serve as a proportionately sized aggregate input to an energy category.

I lightened the colors on the input boxes (had to do it), but otherwise I think my rendition is faithful to the original. I may have stayed too true to flow pipe proportionality. Some of them are so thin they do not print well. This should be addressed. While my pipes are seemingly lined up, they will not survive the chart being resized vertically without some small errors, either gaps in a pipe stack, or overlap.

From this chart I learned a great deal about our country's energy budget. I had no idea that electrical production and transmission was so wasteful (as in 69% waste!), nor did I realize that natural gas was so versatile. Likewise I did not realize that coal produced such a large percentage of our electricity while oil produced virtually none. 

So how much is 99.2 Quads of energy? A Quad is a quadrillion BTUs, or 1,000 trillion BTU. So basically, the US uses a hundred million billion BTUs of energy each year! And 57% of that is wasted! I think we can do better.

I'm not an environmentalist, but all of this world's energy comes from the sun at some point, save nuclear, so it's amazing that more of our energy is not directly solar. Solar is currently less than 1/10 of 1 percent! If I were in charge of energy policy, I would move our focus to solar power generated on as many homes and buildings as possible to eliminate the transmission waste and fill the gaps with natural gas for all other energy requirements. 

An interesting project would be to create an Excel addin that would allow you to specify category box locations and have VBA do all of the grunt work in lining up the flow pipes, automatically creating the chart.

However, in it's current form, dissecting the chart may prove instructive, as quite a few advanced techniques are used to create it. In it's current form no VBA was used. But it's definitely a chart. You can move the location of everything on it by just altering the data.

Here is the chart:

This one's pretty easy. Can you figure out how the Excel chart for this week's optical illusion was created?

I'll have another Excel Optical Illusion next Friday.

Here's the chart:

The first thing you'll notice is that this is not a typical Excel-style chart. In fact, I'd wager it was created in Illustrator. Since the chart has flair I set about creating an Excel version. I grabbed the data from Yahoo Finance. I wanted to be faithful to the look and feel. I sent my first attempt to Ajay and he was kind enough to post it on his blog.

While I think I succeeded in capturing the look and feel, I was not happy at all at the hit it put on Excel. It took over 30 seconds to render on my computer and my computer is no slouch. I was compelled to improve this performance. The new version renders the chart in less than 1/2 a second. A massive improvement, and remarkable when you consider that over 20,000 days of data are being graphed.

The performance was good enough that I added a scroll control so that the user can zoom into the data. Right now the zoom point is fixed at the year 2010. I added this just to show the speed of the chart. I'll leave it to the reader to enhance the chart so that the user can scroll through the years as well as zoom in and out.

I really like how true outliers fly off the main section of the chart. You never see this on Excel charts because if your chart has grid lines, the grid fills the entire Plot Area so all data including outliers will be on the grid. The solution here was to not use the built-in grid lines, but instead to draw them with chart series.

The years along the horizontal axis are interesting and are not plotted by the Axis.

The colored bar behind the daily fluctuations represents how volatile each year was and visually gives an interesting backdrop to the chart. Can you figure out how it was charted?

Tearing the chart apart can be very instructive.

Here's the chart: