Gas Prices – Animated Bar Chart for Excel 2
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Gas Prices – Animated Bar Chart for Excel, I duplicated Nathan’s dynamic bar chart of gas prices in Excel.
It’s a pretty nice animation, but there is no sense of where the bar chart is along the date scale of the data. I made a small adjustment to the model above, drawing a small time line of the US Average, with a vertical line indicating the date of the bar chart.
The line chart contains two series. The first is a time series of the national average data. The second series contains only one potted point, the value of the national average at the selected date; an error bar for this point provides the vertical line. This data is in column L of the data sheet. The formula in cell L10 (the first row of price data) is
=IF(A10=A$2,J10,NA())
A10 contains the date for that row, A$2 contains the selected date, J10 contains the national average for that date. If the date for that row matches the selected date, then the formula returns the national average, otherwise it returns #N/A, which is not plotted in a line or XY chart. The vertical error bar applied to this series uses the negative Y option and a percentage of 100%. This formula is filled down to the bottom of the price data.
The VBA code in this new workbook is unchanged, and the bar chart is also unchanged, other than a lighter shade of gray filling the bars. Only the line chart and thee column of data for the vertical line in his chart have been added.
To try out the new animated chart, download this zip file. It is unlocked and unprotected, so you can see the code and watch how it works.
Update
I showed the data in an intereactive time-series in Gas Prices – Interactive Time Series.
Possibly Related Posts:
- Gas Prices – Animated Bar Chart for Excel
- Gas Prices – Interactive Time Series
- Moving Averages
- What Goes into the Price of Gas?
- Interactive Multiple Line Chart
- Interactive Charts with Checkboxes and Formulas
- Mind the Gap – Charting Empty Cells
- Easier Interactive Multiple Line Chart
- Order of Points in XY and Line Charts
- Link Chart Text to a Cell
Posted: Saturday, August 9th, 2008 under Dynamic Charts.
Comments: 5
Comments
Comment from dermot
Time: Sunday, August 10, 2008, 5:26 am
Jon
I know you set out to reproduce the animation, but I think there are better ways to show this data.
I would average the data over periods of 3-6 months and show all the lines and dates on the same (static) chart, perhaps allowing the user to pick one series to highlight.
As it is, it takes far too long to run through all the weeks and all the patterns are lost.
Comment from Andy Cotgreave
Time: Monday, August 11, 2008, 4:26 am
Excellent stuff, Jon. The line chart animation is really nice. But, as dermot says, and you probably agree, in the end, the line chart alone is the best way to show this data!
Thanks for showing a nice way to make this visualisation in Excel, though. Very useful.
Andy
Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 12:57 pm
Dermot – I showed the data in an intereactive time-series in Gas Prices – Interactive Time Series.
Pingback from Charts of the Week [Aug 15] | Pointy Haired Dilbert – Chandoo.org
Time: Friday, August 15, 2008, 10:08 am
[...] friend Jon takes a look at this and recreates it in excel. Read more.[via [...]
Comment from DaleW
Time: Monday, October 19, 2009, 9:56 pm
Jon – Nice animation. Yes, your interactive time series chart is a much more efficient way to look at the data.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.