July 2010 Archives

Excel Optical Illusions Week #24

| 0 Comments | 0 TrackBacks
 This week's Excel Optical Illusion is called the Reverse Spoke Illusion.

reverse_spoke_optical_illusion_excelhero.com.gif
Which way are the spokes moving? 

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

... The wheel defined by the dark-bright sectors is rotating clockwise and the spokes are rotating counter-clockwise?

Look again... The spokes stay put! Rather, each spoke makes a tiny shift in one direction and then slowly (subliminally) moves back.

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.

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:

 - 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)



Excel Optical Illusions Week #23

| 1 Comment | 0 TrackBacks
This week's Excel Optical Illusion is called The Freezing Rotation.

freezing_rotation.gif
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...

... you see (1) an inner rotating part (the text) placed on (2) a rotating disk made up of coloured dots. The outer disk does not rotate continuously, but 'swings' back and forth. What is the motion of the central text? Is is a smooth rotation? 
Or doesn't it rather 'freeze' now and then, depending on the outer rotation?

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.

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:

 - 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)


Enhanced by Zemanta

Animated Business Chart #2

| 11 Comments | 0 TrackBacks
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.

freshbooks_animated_chart_excelhero.com.gif

 
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)

Enhanced by Zemanta

Excel Optical Illusions Week #22

| 1 Comment | 0 TrackBacks
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.

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

In the perpetual collisions illusion, the pink and the yellow columns seem always to be headed towards (or away from) each other, but they never meet (and they never grow further apart). Actually, the colored fields are completely stationary; an appearance of motion is generated by the spinning black and white diamonds located alongside the columns. Click on the button to add diagonal bars and remove the edges from opposing diamonds. Notice that the information at the edges makes the colored fields move diagonally, yet when the bars are not there and all the edges are visible, the fields move horizontally.

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.





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:

 - 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)







Enhanced by Zemanta

Excel Partial Match Database Lookup

| 32 Comments | 0 TrackBacks
Here is a technique that I've used countless times when crafting an Excel database project for clients. The goal is to show all records that partially match a lookup string.

partial_match.gif
In the sample file, there are 10,000 records and as you can see the partial match lookup is very quick. It supports wildcard characters.

 
While this is entirely an Excel application, what we are doing here is equivalent to a traditional database SQL statement of:

SELECT *
FROM Data
WHERE (((Data.Description) Like "*search_string*"));

So how do we do this in Excel. It could be done using just formulas, but this is a VBA project. It leverages the brilliant FindAll() function by Chip Pearson. All of the code is in the Sheet2 code module. Have a look. It's succinct, potent.

The file works in all versions of Excel from 2000 forward.

So tell me how you do partial match lookups?

Here is the file.




Please remember to sign-up for the free Excel Hero Newsletter!



--- UPDATE ---

As we have seen many times before,  Excel always has multiple ways of doing things. Sam sent me an awesome tip. I revised the workbook to use the tip and it is now much faster and the code is better too. Chip's FindAll() function is still brilliant, but it was not needed here, so I've removed it in the new version. It is useful for many, many things and I'll write about it often. Sam's tip was to use the AdvancedFilter method of the Range object. The previous version was more than adequate as long as the search string was three or more characters long. Now it's instant even if one character long. In fact, it's instant even if zero characters long!

Here it is.





--- UPDATE #2  ---

As promised in the comments, here is the ADO way of doing the Partial Match Database Lookup. I have taken Nikolai's example and reworked the code somewhat to conform to my style of programming. Using ADO is very, very powerful, as it unlocks the entire universe of real databases, such as SQL Server, Oracle, MySQL, Access, and countless others. Virtually all Windows computers have ADO installed and ADO can read Excel files even if the machine does not have Excel installed! Of course for our purpose, we will be using it from Excel so that is beside the point. But you see, ADO, is a separate library that dozens of programming languages can use. So to use it from Excel we need to reference the ActiveX Data Objects Library from the Tools-References menu of the VBA Editor.

Over the coming months I will share many applications that use ADO to interact with databases. But there is one hiccup that you need to know about. If you are using it to interact with Excel data in the same workbook where you are using ADO, there is a problem. Each time you call the ADO library, a bit of memory will be used up on your computer and you will never get it back until you reboot your machine. This is called a memory leak, and it is poor programming practice. This particular memory leak is a recognized bug and Microsoft has published a KnowledgeBase article about it on their support website:


So you should never do this. However, for instructional purposes I have setup Nikolai's workbook with a check box that allows you to toggle between using local data, and having the VBA code create a copy of the data in a new workbook and accessing that instead (which is the correct way to do it). To see the memory leak in action, just open the Windows Task Manager to the Processes tab while you have the workbook open. Sort the list by Image Name. Find the Excel.EXE process and select it with the mouse. Now start using the Partial Match Database Lookup with ADO workbook to do searches. You will notice if you  have the External Data Method check box checked that the memory will temporarily increase for the Excel.EXE process as ADO is invoked; but you will also notice that the memory is quickly given back to the Windows operating system. This is good and the way things are supposed to work.

Now un-check the External Data Method check box in the workbook and do some searches. You will notice that each time a search is done the memory spikes, but that memory is NEVER released back to Windows. This is the memory leak. If you keep using ADO in this fashion eventually you will run out of memory and your computer will crash. The only way to get that memory back is a reboot.

So this file is interesting and instructive. It shows you how to use a very powerful library to access data, and it demonstrates the correct and incorrect way of doing so, allowing you to toggle back and forth between the two.

Thank you Nikolai for the file. He runs a fantastic website on chess, so please check it out:


Here is his modified Parial Match Database Lookup file that uses ADO:







Enhanced by Zemanta

Excel Formulas Based Sudoku Solver

| 6 Comments | 0 TrackBacks
About four years ago, I created an interesting Sudoku Solver. It was unique because rather than use brute force VBA to determine every possible combination of numbers to solve a given puzzle, it used recognized human logical techniques that had been developed for the Sudoku game by enthusiasts worldwide. Techniques such as Intersection Removal, Hidden Quads, and Naked Pairs. And to top it off, these logical techniques were executed by formulas, with the heavy lifting done by named formulas using bitwise operations. I alluded to this game solver back in January when I posted 5 And 3 Is 1. Today, I'm finally sharing it with you.

 
sudoku_solver_excelhero.com.gif
Above is an animated GIF of my Sudoku Solver in action. It does use VBA, but not for solving a puzzle, just for updating the display and moving the game forward. What's nice about this implimentation is that it is interactive and can be used to learn the various human logic techniques that when mastered can solve these puzzles efficiently.

The techniques that are currently supported are Naked Singles, Hidden Singles, Naked Pairs, Hidden Pairs, Naked Triples,  Hidden Triples, Naked Quads, Hidden Quads, and Intersection Removal. I had planeed to add X-Wing, Turbot Fish, Y-Wing, Sword-Fish, Jelly Fish, and X-Wing Pairs, but sadly never found the time. Perhaps a budding Excel Hero will. The project is open-ended so any number of logical techniques could in theory be added.

I'll warn you that the logic gets a little complex. I'm sure my formulas could be improved upon. The project was done as a proof of concept and not as a production piece. However, there is a lot here that can be learned from advanced named formulas to VBA to conditional formatting.

To run the solver, just click on the Search button. The program will test the puzzle to see if any of the logical techniques in its arsenal will work. When it finds one, it stops and highlights the technique and the affected squares. This is how you learn. To continue, just click on Search again. This iterative process continues until either the puzzle is solved or the current roster of techniques cannot advance the puzzle solution further. Surprisingly most puzzles published can be solved with this limited list of techniques.

The small numbers are candidates for the larger square immediately to their right.

You will notice a sheet named "z." On that sheet is a collection of puzzles that you can try. Most can be solved by this program. Some cannot. Just copy the 81 numeral string to the clipboard and then switch back to the Game sheet and click the Import button. Paste the string in the dialog and click OK. You can import any Sudoku puzzle this way; it just needs to be in the standard 81 numeral format.

On the bottom-right corner you will find a section entitled Show Candidates. Clicking on any of the colors there will highlight the respective candidates on the game board.

The program goes out of its way to protect itself and password protects the Game worksheet on every move. The password it just the numeral "1". Tear into this. I guarantee you will learn a lot.

I've tested it in Excel 2002, 2007, and 2010. Please let me know what you think.

Here is the file.


Enhanced by Zemanta

Excel Optical Illusions Week #21

| 1 Comment | 0 TrackBacks
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:

two_sinusoids.gif
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:


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:

 - Excel, A Presentation Platform (Number Spiral)
 - Lilac Chaser (Optical Illusion)
 - Stereokinetic (Optical Illusion)
 - Illusory Contours (Optical Illusion)
 - Breathing Square (Optical Illusion)
 - Enigma (Optical Illusion)

Enhanced by Zemanta

Excel Art

| 3 Comments | 0 TrackBacks
In my day job, I've been steeped in very complicated programming for clients as of late. I needed a break and so this evening I decided to unwind and make something beautiful. I call it butterfly rainbow.butterfly_rainbow_excel_art.gif

I'm satisfied with how it turned out, although the animated GIF does not do it justice. Please download the workbook for a better experience.

 
This is a Radar chart with named formulas for the series. The VBA is used solely to animate the colors. This project is simple and instructive. Have a look and let me know what you think!

Here is the file.



If you would like to see more Excel Art, here is a beautiful and very large poster I created to demonstrate Benford's Law. I think it is absolutely stunning!

And here are my two ALL TIME favorite Excel Art creations. They incorporate sound!



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)
Enhanced by Zemanta

Excel Optical Illusions Week #20

| 7 Comments | 0 TrackBacks
This week's Excel Optical Illusion is called the Dynamic Luminance-Gradient Effect and was modeled after Alan Stubbs's creative work.

dynamic_illuminance_effect.PNG

Just lean forward toward your monitor and back again to see the luminance optical illusion.

This is a Radar chart with radial gradients for the series and the plot area. The file is an .xls and so will work (sort of) for older versions. Radial gradients are new so versions previous to Excel 2007 will render the gradients as a rectangle, with muted colors, but interestingly the illusion still presents!

Here's the file.


If you like my work, 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

About this Archive

This page is an archive of entries from July 2010 listed from newest to oldest.

June 2010 is the previous archive.

August 2010 is the next archive.

Find recent content on the main index or look in the archives to find all content.