Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | Peltier Tech Blog

Peltier Tech
Chart Add-Ins

Excel Dashboards

Books at Amazon.com

Reciprocal Axis Scale.

Many physical phenomena exhibit behavior that shows faster rates or greater solubilities at higher temperatures. An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:

K = A exp (-Q/RT)

where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:

log (K) = A' - Q/RT

A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A'. This page describes the construction of one variation of this type of chart.

Microsoft Excel does not offer a built in capability to chart reciprocal data, but the technique described here allows you to simulate a reciprocal scale along a chart axis (the horizontal axis in this example). This is related to the Flexible Log Axis Scale technique described by Tushar Mehta on his web site, http://www.tushar-mehta.com. A dummy series is plotted along the X axis, and formatted to look like an axis, with tick marks and labels. These labels are applied to the dummy axis series using the XY Chart Labeler, a free add-in written by Rob Bovey and available at http://www.appspro.com. Download and install Rob's Chart Labeler before getting too involved in this example.

This reciprocal axis chart is an application of the Arbitrary Axis Scale and Arbitrary Gridlines examples shown elsewhere on this website. Another implementation of these techniques is shown in my Simulated Probability Chart Example.

The tables below show the data I used to construct the simulated X and Y axes in this example, and the dummy experimental data I used. The only significance of this data is that I chose it to fall roughly on a straight line on the log K - 1/T chart. This line has a positive slope because the reciprocal temperature increases from right to left.

Dummy X Axis

Temp (°C)   1000/K    Y
---------   ------   ---
  -20°C     3.9526    1
  -10°C     3.8023    1
    0°C     3.6630    1
   10°C     3.5336    1
   20°C     3.4130    1
   30°C     3.3003    1
   40°C     3.1949    1
   50°C     3.0960    1
   60°C     3.0030    1
   70°C     2.9155    1
   80°C     2.8329    1
   90°C     2.7548    1
  100°C     2.6810    1
  110°C     2.6110    1
  120°C     2.5445    1
  130°C     2.4814    1
  140°C     2.4213    1
  150°C     2.3641    1
  160°C     2.3095    1
  170°C     2.2573    1
  180°C     2.2075    1
  190°C     2.1598    1
  200°C     2.1142    1
  210°C     2.0704    1
  220°C     2.0284    1
  230°C     1.9881    1
  240°C     1.9493    1

Dummy Y Axis

   X      Y    Label
------   ---   -----
3.9526     1     1
3.9526     2     2
3.9526     3     3
3.9526     4     4
3.9526     5     5
3.9526     6     6
3.9526     7     7
3.9526     8     8
3.9526     9     9
3.9526    10     1
3.9526    20     2
3.9526    30     3
3.9526    40     4
3.9526    50     5
3.9526    60     6
3.9526    70     7
3.9526    80     8
3.9526    90     9
3.9526   100     1

Dummy Data

Temp (°C)  1000/K     Y
---------  ------   -----
   -15     3.8760    1.28
     3     3.6232    2.16
    14     3.4843    3.11
    25     3.3557    3.94
    39     3.2051    5.56
    52     3.0769    7.33
    58     3.0211    8.34
    80     2.8329   12.25
    95     2.7174   16.14
   111     2.6042   20.65
   121     2.5381   23.93
   144     2.3981   32.14
   160     2.3095   38.82
   180     2.2075   48.64
   192     2.1505   54.20
   202     2.1053   61.66
   230     1.9881   80.00

The temperature columns contain numbers with a custom number format of "0°C". The reciprocal temperatures were defined as possible: For a Celcius value in cell A2, the formula in cell B2 is:

=1000/(A2+273)

Y values for the Dummy X Axis correspond to the minimum on the Y axis. The original Celcius temperatures are used for the X axis labels, because they are more meaningful than the transformed reciprocal temperatures. X values for the Dummy Y Axis correspond to the maximum on the X axis (the values are charted in reverse order). The Y axis labels were chosen to show one digit, including minor tick labels, and not have extraneous zeros as the scale moved to hundreds or hundredths.

I started constructing the chart by selecting the Dummy X axis data (1000/K and Y), and running the chart wizard to generate an XY Scatter chart. I selected the Dummy Y axis data (X and Y), copied it, selected the chart, selected Paste Special from the Edit menu, and checked the New Series, By Columns, and Categories in First Column options. Without any further formatting, the chart looks like this:

I formatted the X axis as follows:

Scale tab

Minimum Scale: 1.949318
Maximum Scale: 3.952569
Values in Reverse Order
Value (Y) Axis Crosses at Maximum Value

Patterns tab

Major Tick Mark Type: None
Minor Tick Mark Type: None
Tick Mark Labels: None

I formatted the Y axis as follows:

Scale tab

Minimum Scale: 1
Maximum Scale: 100
Logarithmic Scale

Patterns tab

Major Tick Mark Type: None
Minor Tick Mark Type: None
Tick Mark Labels: None

I formatted both dummy series as follows:

Patterns tab

Custom Marker Foreground: Black
Custom Marker Background: No Color
Custom Marker Style: Cross
Size 6

If you format one series, you can then select the second, and press F4 to repeat the formatting on the second series. The chart now looks like this:

Next I added Y axis minor gridlines to the major gridlines present by default. This is one of the options in the Chart Options dialog from the Chart menu, or available when you right click on the chart.

To get X axis gridlines, I used error bars on the dummy X axis series. Select the series, press Ctrl-1 to open the Formatting dialog, click to the Y Error Bars tab, click on the Plus error bars button, and for fixed value, enter 99, which is the difference between the Y axis maximum and minimum values. Now select one of these error bars, press Ctrl-1 to format these. Select the style without the cross at the end, and choose the hairline weight to match the Y axis gridlines.

Before adding axis labels, I deleted the legend. I changed the size of the plot area, to allow room at the left and bottom margins for the labels.

I used the XY Chart Labeler to add the dummy axis labels to the dummy axis series. The Labeler runs off the new XY Chart Labels command at the bottom of the Tools menu. Select the chart, and run this command:

Select Series 1 in the first dropdown box, use the mouse to select the range containing the Celcius temperatures, and finally choose Below as the position of these labels. I rotated these labels to reduce the overlap between adjacent labels.

Repeat the Add Chart Labels command to put the dummy Y labels to the left of the Series 2 markers. In the full size chart, the labels look great. In the smaller version I made for this exercise, the labels are still too large and too close. I shrunk the font size and removed a few of the cluttered labels:

Finally I added the simulated data, to produce the chart at the top of this page. A zipped workbook is available with sample data and an example reciprocal axis chart.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile