P & V chart: Secondary X axis gradient (again)

[wlotto]

It did not work. There may be some trick with it but I
couldn't find it either.

OK let me explain a bit more.
Category data is one year date, yet, it is not 365 days.
Some skipps here and there throughout, therefore, there
is a space among columns accordingly. I am trying to fill
the space out by creating secondary X axis. (I hope I am
doing right.)

By doing so, I can successfully fill the space by making
a bit of change (making weight thiker, etc.) But I don't
know how to make the area (that is a plot area without
secondary X axis) gradient.

Thanks.

 

[Jon Peltier]

I'm not really sure I understand what you're doing. If you have time
based data with gaps, and you don't want the gaps to show, you can
change to a category axis. Right click on the chart, choose Chart
Options from the pop up menu, and on the Axes tab, change the X Axis
from Automatic or Time Scale to Category.

- Jon

 

[wlotto]

'Category' of Value(X)Axis (Primary axis) is dimmed (both
Automatic and Timescale are also dimmed).

I can select Category for Secondary Axis (Category(X)
axis). When I select this, every problem happens.

Why selections for Value(X) axis are dimmed?

thanks.

 

[Jon Peltier]

I'm going to start over.

In your first post, you said you want a chart with volume as columns and
price as XY. This will also work with the price on a Line chart.

If the data is arranged like this, it will be easy to make the chart:

Date Volume Close
3/2/2004 14044549 98.24
3/3/2004 13637071 107.55
3/4/2004 11787985 95.96

1. Select the data and make either a Line or a Scatter chart.
2. Right click on the Volume series, select Chart Type from the pop up
menu, and choose the Stacked Column option.
3. (see NOTE below) Double click on the X axis, Scale tab, check Value
Axis Crosses at Maximum Category.
4. Double click on the Price series, click on the Axis tab, and choose
Secondary.

NOTE: If you make the Volume series the secondary series, it will make
formatting the X axis more difficult. If you want volume on the right
axis, and price on the left, follow all steps. If you want volume on the
left and price on the right, skip step 3.

If you follow these steps, whether you choose Line or Scatter for your
Price chart type, the chart will be substantially the same. Don't let
the name of the chart type fool you. Either type series can be formatted
to look the same: with or without markers, with or without connecting
lines.

- Jon

 

[wlotto]

Data is like this. Some dates are skipped. One day or
sometimes one week (Real data covers for a couple of
years.) What I have to do is:

1. Close - left Y. Volume - right Y.
2. Beginning date and ending date have to be shown
exactly at the ends of X axis.
3. Skipped dates cannot have spaced. Has to be shown
continuously.
4. Change the background (only plot area) to two color
gradient.

*BTW, why stacked column? Not a clustered column?

Date Volume Close
3/2/04 14044549 98.24
3/3/04 13637071 107.55
3/5/04 10899971 98.30
3/6/04 9771689 97.16
3/7/04 11143407 96.02
3/9/04 9886843 93.74
3/10/04 8758561 117.6033333
3/11/04 10130279 116.4633333
3/12/04 9001997 115.3233333
3/14/04 6745433 113.0433333

 

[Jon Peltier]

Here's how to deal with your constraints.

#1 was covered in the NOTE under my previous process.

#2,3: Make the initial chart as a Line chart. Double click the X axis,
and uncheck the Value Axis Crosses Between Categories box, to put the
end dates on the extreme ends of the axis.

#4: Double click on the plot area, between plotted columns and markers.
Click the Fill Effects button under the Area color palette. On the
Gradient tab, select the options that produce the gradient you desire.

And with only one column series, it doesn't matter whether you choose
clustered or stacked. In fact, I'd intended to write clustered.

- Jon

 

[wlotto]

It worked almost all but,

1. The data is covering over two years and does not have
data point every single day. It skipps like 1, 4, or 7...
days, or sometimes no skipps at all. Skipped dates come
out to be space on columns. (eg. 500 data points in two
years.)

2. When I change 'Chart Option>select Category' in order
to fill the space between columns, the ending date
changes to some obscure date, not the last date of data.
I don't know how to scale it. Scale tub shows no longer
usual format.

2. Any other way with XY schatters start, not with two
lines?

thanks.

 

[Jon Peltier]

1. If you use a line chart, you can eliminate the skips by changing to a
category axis. Right click on the chart, choose Chart Options from the
pop up menu, and on the Axes tab, change the X Axis from Automatic or
Time Scale to Category.

2. A category axis will only go as far as the data goes. Does your X
data extend down further in the worksheet than your Y data?

3. Double click the series, and on the Patterns tab, set the line style
to None.

- Jon

 

[wlotto]

Yes, it eliminated skipps. However, as I said, dates get
screwed up at the same time!!

I need to show the first and last dates on the ends on X
axis. (eg. if the data spans like 3/14/00-2/12/04, the
left most has to be 3/14/00. the right most has to be
2/12/04!

When I change from Auto to Category, the ending date
changes to, say, 1/20/04 or the best was 2/11/04.

Isn't any way with XY chart instead of Line?

 

[Jon Peltier]

If you have a lot of points, Excel will only give you some of the
labels. If it skips every second point, you have a 50% chance that your
desired last value will appear (1,2,3,4,5 becomes 1,3,5). Even in an XY
scatter chart, you can coerce Excel to starting and stopping an axis
where you want, but if the ending date falls between major ticks, it
will not be shown.

You could hide the regular axis labels, and add your own labels, using
the Arbitrary Axis Scale technique from my web site:

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

- Jon