UDF, add-ins and sharing files

Here is one comment posted recently :

"I am having some trouble with sharing files.
If I pass my file to a co-worker who has installed the add-in,all the formulas fail to work and checking them , cell formula changed to :
='C:\Documents and Settings\myusername\ApplicationData\Microsoft\AddIns\Sparklines_3.2.2.RNO.xla'!linechart('PI Data2'!I11:I299,,0,150,G13/24)"

Annoying indeed...

The first versions of sparklines were Excel files that could be used as templates, with the advantage that the VBA code was embedded into the file, so no problem to share those files.

This no longer the case with add-ins.
The *.xla file, containing the code is saved in your add-in library, which location changes for each different user :C:\Documents and Settings\myusername\ApplicationData\Microsoft\AddIns\

To solve this, the add-in release I just posted (3.2.3 for XL2003 + 3.2.6 for XL2000) will now be installed in C:\Program files\Sparklines... and nowhere else !

This should satisfy many users, but I already can hear "I am working wit Citrix", "I have not admin rights, I cannot install anything on C:"... one step at a time, we'll address that later.

PS : Remember to uninstall the previous versions of the add-in (last option of the Sparklines menu) before installing this new release.

PS2 : I'll be off-line for 2 weeks... absolutely unreachable (yes, it is still possible !), mountain climbing in Argentina between Mount Incahuasi (6.610m) and Pissis (6.795m)... see ya!

First example file

Nixnut posted an example workbook on how to use Sparklines for Excel functions in dashboards.

This file mimics the winning dashboard of the 2006 Data Visualization Contest.

You can read Stephen Few's analysis of the original dashboards here.

Release 3.2.3 for Excel 2003

I just posted a new release for Excel 2003 here.

This version includes logarithmic horizontal scales (required for the OECD file of my previous post).

It also brings some changes on Nixnut last statistical functions : StripeChart and SpreadChart used to analyze a population distribution.

It solves a clipboard issue (the clipboard was erased whenever the "Sparklines" menu was re-created).

I hope you will find those improvement helpful.
In the future I will post some samples on how to use the function, individually or organized within a dashboard.

How to use BoxPlot charts

Jon Peltier wrote a post on his blog regarding a chart from The Economist on “Income distribution by decile in selected OECD countries”.

Here is a simple interpretation of this chart using "boxplot" charts with standard and logarithmic scale :

You can download the excel data file here and use it once the Sparklines for Excel add-in is installed on your computer.

Release 3.2.5 for Excel 2000.

I have had a great deal of help from Holland during the last weeks.

Nixnut is using XL-2000. He is very knowledgeable about statistics, and rewrote some (big) parts of the code.

Here is Nixnut last XL-2000 version of Sparklines for Excel.

This release bring some new charts :
StripeChart : It shows a vertical marker line for each value in the range and optionally a taller red line for the average mean. The result is a graph with the distribution of the values in the range.


SpreadChart : A lot like the StripeChart except it shows the frequency distribution of the values in the range in one of three styles: stacked (default), spread or jittered.
It uses dots for the data points.

XBoxPlot (Extended Boxplots). A bit experimental. These boxplots take a range as input and calculate values for whiskers, hatch marks, boxes and outliers as necessary.
There are different classes of boxplots: Tukey (default), 5NS (five number summary), 7NS (seven number summary), and Bowley.
There are also two styles: Classic (default) and Neo. The Neo style shows bars instead of whiskers.

Visible enhancements of existing functions :

• Forecast marker for Bulletchart and RevBulletChart
  

Invisible enhancements :

• Improved "Redraw" and "Delete" routines.
  
• Version number not included in file name, for easier upgrade.

A great deal of very useful Sparklines, dense, self explanatory and easy to embed in dashboards.

This new version is not documented yet...

Well, it seems that I will have to catch up with the XL2003 version... any volunteer for XL2007 ?