This chart might be tough

  • Thread starter Thread starter DaveD
  • Start date Start date
D

DaveD

I'm trying to create a chart known as a "Stiff" diagram
that plots chemical ionic concentrations on the X-axis
with the cations in the left Y axis and anions in the
right Y axis. The ionic concentrations should be scaled
logarithmically to the centre. The X scale should then
read 10,000 to 1 to 10,000 (left-to-center-to-right).

The best analogue I can think of a type of tornado chart
that is logarithmically scaled, but since you can't have
negative logarithmic numbers, the formatting might be
tough. I've been using radar charts, it's not a standard
presentation.

Any ideas?? Thanks in advance.
 
The way I have done this type of chart is to plot the
cation and anion charts separately on the same sheet as
X,Y plots with the ionic concentration for the x values
and a different integer for each of the chemicals with
which you are working for the y value. set the x axis to
logrithmic and set your max and min values for the
charts. you will have to play a bit to make the "1" chart
edges line up . This will give you a double line chart.
if you want other types you can create them with a little
playing but again two charts on one page with the x axis
reversed on one.
 
Of course, one can have negative log numbers! In fact, that's what we
will use to create the chart you want.

Suppose your data are:

B C
2 10000 9000
3 8000 7500
4 3000 4000
5 100 500

Then, in F2, enter =LOG(1/B2,10) and in G2 enter =LOG(C2,10)

Copy F2:G2 to 3:5.

Plot F2:G5 as a bar chart. Double-click either of the plotted series,
and from the Options tab, set the overlap to 100%.

In I2:I10 enter the numbers -4, -3, ... 0 ... 3, 4. In J2:J10 enter
all zeros. In K2 enter the formula =10^ABS(I2) Copy K2 to K3:K10.

Select I2:J10. Copy. Select the chart. Paste Special. In the
resulting dialog box, ensure that 'New series' is selected and that
'Category (X values) in first column' is checked.

You won't see the series just plotted. To select it, select the 2nd
series of bars and use the up arrow. Then, select Chart | Chart
Type... | XY Scatter chart. Pick a subtype with no lines.

Select Chart | Chart Options... | Axes tab. Uncheck both secondary
axes.

The dummy series we added should be along the bottom of the chart.

Use either Rob Bovey's XY Chartlabeler or John Walkenbach's Chart Tools
to add K2:K10 as the data labels for the dummy series.

Remove the labels from the various axis for the desired effect.
Double-click the axis and from the Patterns tab set the 'Tick Mark
Labels' to None.

Above tested with 2003 but should work with earlier versions of XL.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top