Floatig Tornado Chart

  • Thread starter Thread starter terryspencer2003
  • Start date Start date
T

terryspencer2003

I have two sets of values:

Set 1 Set 2
Max 10,000 11,500
Min 8,000 7,500


I want to compare these values to a mean value of say 9,000.

I want to built a floating bar chart that expresses the max and min of
each set to the mean value fo 9,000. I want the X axis to cross at
9,000. I then want to show the 10,000 as a 1,000 incremental over the
X axis value and then the 8,000 as a (1,000) decrement to the X value.
Accordingly I want to show the same thing for Set 2.

I only really want to have a floating bar chart for the spreads of the
mins and maxes. So if by chance the min max are both higher (i.e.
10,000 and 9,500) than the X axis value of 9,000 then I want to see a
space between the axis and the floating increment. Likewise for a
lower value.

I can built a chart like this but the problem is, that if both values
are higher than the axis, the chart shows two bar increments. One for
the difference in the min value and the axis and one for the min max
spread. And when these values are less then the X axis, the chart
shows only one bar but it is simply the difference of the min and x
axis.


Can anyone tell me how to do this?

TS
 
Terry -

Making the first chart you describe is pretty straightforward. Clustered
bar chart, series in rows, category axis crosses value axis at 9000
(double click value axis, Scale tab), 100% overlap of the series (double
click either series, Options tab). How do you want to show the 1000 and
(1000)? As data labels?

The second chart needs different data:

Set 1 Set 2
Max 10,000 11,500
Min 8,000 9,500
Spread 2,000 2,000

where the spread cells have a formula to compute the max-min values. I
changed the min of set 2 to test the floating bars and the axis.

Make a floating bar chart of the min and spread data, series in rows,
category axis crosses value axis at 9000 (double click value axis, Scale
tab), but leave overlap at 0%. No matter** where the spread is located
(straddling the repositioned X axis or not), it will span the range
between min and max. There's a gap between the axis and the floating bar
if the data requires it.

** If min is <0 and max >0, this is not the case. I give some
instructions for this case here:

http://peltiertech.com/Excel/Charts/StackedColumnsAboveAndBelow.html

- Jon
 
Back
Top