Codeless Excel Fun

When it comes to the typology of Excel users, I definitely fall into "programmer" type, that is, I prefer using VBA even for simple tasks and generally avoid using formulas. On the other hand, from time to time, while reading various Excel forums, I'm really amazed how some Excel gurus solve different challenges solely with the help of smart formulas.

While codeless worksheets could prove perfectly useful in accounting or financial analysis, is it possible to create anything entertaining in Excel without VBA? The answer is, of course, yes!

When thinking about formula fun, the first thought invariably flows to the Excel RAND function. On each recalculation, it draws a number between 0 ad 1 from the uniform random distribution. And having a set of easy-to-refresh random numbers create a possibility to make some funny experiments.

Jocelyn Paine send me a very cool workbook which generates a science-fictions plots with a simple transition network. This is both very funny workbook and an interesting tool to study the recursive algorithm. The generator requires Analysis Toolpak to be installed, but it is purely formula based (it asks to enable macros, but only because of empty VBA module). You can download it from Jocelyn's website:

http://www.j-paine.org/excelsior/repository/spin/index.html

Thinking about combining random numbers and conditional formatting I created a simple generator of 6/49 Lotto drawings. By pressing F9 you force RAND formulas to refresh and new combination is shown on the board. When you look into formulas you will notice that formulas behind it are extremely simple, yet they generate uniformly distributed lotto combinations without the re-sampling of random numbers.

The educational value of the workbook is that it shows that random clusters (drawing of adjacent numbers on the board, like 7 and 14) are more probable than people usually expect:

Download 6/49 Codeless Lotto Generator (7kb).

There are certainly many possibilities to construct a funny codeless workbooks. If you write any VBA-free entertaining stuff in Excel and want to share it, please send me your files, and I will include them in this essay.

Update: March 14, 2009. Recently, Stuart Farrington created a very cool Excel game without the use of VBA. You can find his role playing gamebook Codeless in my Library. This game utilized a simple but very interesting concept of creating Hyperlinks with conditioned formulas in Excel.