A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 10th, 2004, 04:13 PM
wlotto
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

  #2  
Old March 10th, 2004, 06:15 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.


  #3  
Old March 10th, 2004, 11:40 PM
wlotto
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

'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)?
.


.


.

  #4  
Old March 11th, 2004, 02:24 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.


.


  #5  
Old March 11th, 2004, 04:01 PM
wlotto
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

.


.

  #6  
Old March 11th, 2004, 04:42 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

.


.


  #7  
Old March 12th, 2004, 12:04 AM
wlotto
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

.


.


.

  #8  
Old March 14th, 2004, 12:04 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

.


.


.


  #9  
Old March 14th, 2004, 02:00 PM
wlotto
external usenet poster
 
Posts: n/a
Default P & V chart: Secondary X axis gradient (again)

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)?
.


.

.


.


.


.

  #10  
Old March 15th, 2004, 06:26 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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)?
.


.

.


.


.


.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.