Dr. Nicolas Bissantz wrote in Do time series charts really compare time series? about time series being difficult to compare. The chart he discussed showed the cost of energy for three different heating methods. Says Nicolas, “The lines suggest that the price for heating oil has exploded, while electricity and natural gas have increased moderately.”
When we calculate the percentage change as in the table above, we see that the middle green curve for electicity has actually risen more slowly than the other two fuels, and the natural gas curve isn’t far behind heating oil in terms of relative increase.
Nicolas goes on to discuss absolute and relative increases, and uses changes in the data (point-to-point changes or slopes) to make his point. HIs point is well taken, that the absolute linear scale on the vertical axis leads to incorrect conclusions. Rather than calculate a lot of slopes, and present the data in different terms than the original chart shows, there’s an easier approach: use a logarithmic value axis. I recreated Nicolas’ table, then connected the endpoints in a linear scale chart and a logarithmic scale chart.
The logarithmic scale on the right preserves the price values, while converting the slopes from absolute changes (where heating oil dwarfs the other two fuels) to relative changes (where electricity shows the slowest rise).
The use of a logarithmic scale is nothing new, of course. Financial data has long been presented on this basis, which facilitates comparisons between a $20 stock and a $30 stock, without requiring the reader to perform mental calculations.
This concludes the theoretical portion of today’s post.
How to make a nice logarithmic scale
Excel allows you to convert a linear value axis to a logarithmic value axis. Simply check the Logarithmic Scale box near the scale parameter settings. The result is okay (below left), but far from perfect. Up through Excel 2003, the axis could only begin, end, and show labels on a power of ten; Excel 2007 allows you to change the base of the log scale, a real improvement, but the labeling options are still limited to powers of the base (below right with a log base of 2).
So how did I get a nice log axis scale above? I rolled by own axis. Here is the data I used. In cells B6 and B7 I inserted “good” endpoints for my scale; I used worksheet cells to make it easy to adjust the calculations. I computed logarithmic values in E2:F4 using this formula in E2 and filling it into the range:
=(LOG(B2)-LOG($B$6))/(LOG($B$7)-LOG($B$6))
I put “good” tick labels in C10:C16, calculated the corresponding axis values in B10:B16 using this formula in B10:
=(LOG(C10)-LOG($B$6))/(LOG($B$7)-LOG($B$6))
and I put the year 1990 into A10:A16. When I construct a dummy XY series for my new axis, A10:A16 will contain X values, B10:B16 Y values, and C10:C16 data labels for the series. The value of 0.5 in cell C9 will control the length of tick marks (i.e., error bars) on this dummy series.
I selected A1:A4, then held Ctrl while selecting E1:F4, so both areas were highlighted, and created an XY chart with series in rows. These are the trends I want to show, but the axis scale does not show the actual prices.
I copied A9:B16, selected the chart, and used Paste Special to add the data as a new series, by column, with categories (X values) in the first column and series name in the first row.
I added custom positive horizontal (X) error bars to this new series, using cell C9 for the custom error bar value.
I hid the series by formatting it to show no line and no marker.
I formatted the error bars to use medium gray to match the axis line color, and to draw just the bar without the end cap.
I hid the Y axis tick marks and tick labels.
Finally I added data labels to the dummy series, using the contents of C10:C16. You can do this by adding any data labels and manually changing the text of each, but I use Rob Bovey‘s free Chart Labeler for this. John Walkenbach makes a similar utility, Chart Tools.
This nicely drawn logarithmic scale chart easily shows relative prices as well as relative changes in price.
More Axis Scale Articles
- Calculate Nice Axis Scales with LET and LAMBDA
- Calculate Nice Axis Scales in Your Excel Worksheet
- Calculate Nice Axis Scales in Excel VBA
- Chart UDF to Control Axis Scale
- How Excel Calculates Automatic Chart Axis Limits
- Reciprocal Chart Axis Scale
- Custom Axis Labels and Gridlines in an Excel Chart
- Custom Axis, Y = 1, 2, 4, 8, 16
- Logarithmic Axis Scales
- Link Excel Chart Axis Scale to Values in Cells
- Consistent Axis Scales Across Multiple Charts
- Gantt Chart with Nice Date Axis
- Select Meaningful Axis Scales
- Bar Chart Value Axis Scale Must Include Zero
Harry says
Jon:
I don’t see how you changed cells A10-A16 to show the time series.
The screenshot shows the cells initially are 1990 (or 1900, from your initial text).
thanks,
Harry
Anonymous says
What method did you use to come up with C10:C16? Was it guess and check, or did you have a mathematical formula to calculate the “good” tick labels? You’ve used Log through several posts now (such as the simple physics problem), do you think you could do a post explaining why logs are so useful, highlighting some of the mathematical reasoning behind Logs… explaining their interpretation?
EXCELlent post… as always.
Jon Peltier says
Harry – Cells A10:A16 are not involved in the time series. They contain the year 1990 (good catch) and provide the same X value for each of the cells in the added series, which has points along the left hand vertical axis.
Anon – In this case, the values were determined “by inspection”. I had made one or two preliminary charts, which led me to decide these values would work well. Originally I was thinking 2-5-10-20-50-100, but I adjusted as I went along.
In some professional applications, I’ve used algorithms to determine these values. Depending on the spacing and the relevant endpoints, I might choose between 1-5-10, 1-25-50-75-100, 1-20-40-80-100, etc., for the general spacing, and use another algorithm to choose where precisely to start and end.
I have a standard protocol I use for linear axes, which has evolved from a VBA technique of Stephen Bullen‘s. I have developed both worksheet function and VBA versions. Stephen has posted his protocol in the newsgroups, but it’s also available in chapter 15 of Professional Excel Development, under “Function to Calculate Reasonable Chart Axes Scales”.
Sjoerd Hoogwater says
Excellent post. It would be even more useful if the data were all in the same units (i.e. cents per kWh) so that we can choose the most economic option.
Using a typical heating value, 1 euro/100 liters fuel oil converts to 0.092 cents/kWh. So, 26.38 euro/100l = 2.43 cents/kWh and 58.63 euro/100l = 5.39 cents/kWh. This lands fuel oil just below natural gas. However, it is somewhat less attractive because it doesn’t burn as efficiently, and not as clean.
Electricity is clearly the least attractive option.
Jon Peltier says
Sjoerd –
This is a good point. I noticed the difference in units, but your conversion into similar units tells an even harsher tale of deception. Have you chared this with Nicolas?
Jon Peltier says
I followed up on Sjoerd’s comment, and replotted the data. I have to admit it’s much less interesting now, on either a linear or a log axis scale. As Sjoerd says, electricity is the highest price option, and the headline about the heating oil is irrelevant.
Siren says
I’ve been trying for some time now to find a way to use a log-like axis scale that includes negative values.
Basically what I’m looking for is:
Tick marks, each an inch apart, on the positive Y axis, labeled: 10, 100, 1000, 10000, …
Tick marks, each an inch apart, on the negative Y axis, labeled: -10, -100, -1000, -10000, …
It just seems wrong that I’m allowed to scale positive values but not negative ones. Can you help? :)
Jon Peltier says
Siren –
The nature of logarithms is that you can only convert positive numbers to logs, with the resulting logs covering the range of ± infinity.
You can always fake it. I have a number of samples on my main site that show various Custom Axis Scales using Dummy Series. Most involve adding a helper (or “dummy”) XY series to create the axis, then transforming the data to accommodate this dummy scale
You would have to decide where your line of symmetry would be. For example, you could have the positive values go 1, 10, 100, etc, and the negatives go -1, -10, -100, etc, with +1 and -1 occurring at the same point. To get your negative values to plot, you need to transform them using something like -log(-value).
Alternatively, you could select a ± range that will be linear in the middle, with log values extending outward from this range. So the scale would go
-100, -10, -1, 0 ,1 , 10, 100, etc., with the data plotted linearly between -1 and +1.
Mario Marinato says
Jon,
Thank you very much for you posting. I’m working on a open source software that plots financial graphs but it had a broken log-scale-plotting-function which I had to fix. After a few days of some misleading readings I discovered your explanation and it gave me the clues I needed to solve the problem.
Your writing is really clear. Congrats!
Again, thank you very much.
Mario Marinato, from Brazil
Rob Beal says
Jon-
Great work and great website, your techniques are very helpful in working within the limitations of excel. Log plotting is a technique that I prefer to use for many data series, but I only do it occasionally because of the extra work at building the charts in excel. Do you know if Microsoft improved log plotting in Office 2010?
Thanks!