P & V chart: Secondary X axis gradient (again)
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wlotto wrote:
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?
-----Original Message-----
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wlotto wrote:
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 Optionselect 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.
-----Original Message-----
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wlotto wrote:
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
-----Original Message-----
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wlotto wrote:
'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.
-----Original Message-----
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wlotto wrote:
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.
-----Original Message-----
Richard -
An off-topic word to the wise. I know it's only
a
hotmail account, but
if you don't do something to disguise it, you'll
be
fighting off
mountains of spam in your inbox.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
wrote:
right click on the 'volume' data series and
select 'format data series'. On the 'Patterns'
tab,
click 'fill effects'. from there you can pick
your
gradient colours.
Richard
-----Original Message-----
I created a typical price&volume graph.
Price/XY,
Volume/Column. I put Secondary Category (X),
Category
in
order not to break columns.
Q. How could I change Volume's color (on
Secondary
X
axis) to gradient (two colors)?
.
.
.
.
.
.
|