A few months ago in Gapminder for Excel I made a simplistic mockup of Hans Rosling’s Gapminder presentation Debunking third-world myths with the best stats you’ve ever seen in Excel. It was fairly simple, with a single bubble marching across a chart, interpolating between yearly data points as a simple timer-based VBA procedure incremented.
An astute reader named Eric thought that the chart would be more effective if the moving bubble left a trail of breadcrumbs, as it were, the annual mileposts along the travels of the moving bubble. I took up the challenge, and it took less time to work up than it’s taking to write this post.
I modified the data somewhat, since it was made up anyway, so that the path traced by the moving bubble was more continuous. I also defined some names in the worksheet which contain the whole series of data from the first point up to the point where the moving bubble has reached.
Here is a summary of the names, if you’re playing along at home. The first three names were present in the first version of this chart, and were used by the VBA program in its interpolation routine. The next three names are the X and Y values and the Diameters for the bubbles. Finally, I’ve included the chart series formula for the new series.
Name Refers To FirstColumn =Data!$B$12 LastColumn =Data!$B$13 ControlColumn =Data!$B$14 AllYearsX =OFFSET(Data!$A$3,0,1,1,GapminderExcel.xls!ControlColumn-1) AllYearsY =OFFSET(AllYearsX,1,0) AllYearsD =OFFSET(AllYearsX,2,0) Chart Series =SERIES("All Years",GapminderExcel.xls!AllYearsX, GapminderExcel.xls!AllYearsY,1,GapminderExcel.xls!AllYearsD)
The workbook can be found in GapminderII.zip.