Thank you John, I have what I want now thanks to the post of Debra.


"John Mansfield" schreef in
bericht ...
Bert,

One way to add the average line is to eliminate the use of the stock chart
and use a combination stacked bar and line chart. To explain, assume your
original data is as follows:

a b c d e
Date Open DHigh DLow Close Average
1/1/2005 55 58 53 56 55.67
1/2/2005 56 57 54 55 55.67
1/3/2005 55 57 53 56 55.67
1/4/2005 56 58 52 57 55.67
1/5/2005 55 59 53 57 55.67
1/6/2005 54 57 52 55 55.67
1/7/2005 53 56 51 54 55.67
1/8/2005 54 58 52 56 55.67
1/9/2005 56 57 54 55 55.67

Rearrange the data to look like this.

f g h i j
Date Low High Average DHigh DLow
1/1/2005 55 1 55.67 2.33 2.67
1/2/2005 55 1 55.67 1.33 1.67
1/3/2005 55 1 55.67 1.33 2.67
1/4/2005 56 1 55.67 2.33 3.67
1/5/2005 55 2 55.67 3.33 2.67
1/6/2005 54 1 55.67 1.33 3.67
1/7/2005 53 1 55.67 0.33 4.67
1/8/2005 54 2 55.67 2.33 3.67
1/9/2005 55 1 55.67 1.33 1.67

Column F is the minimum value of column A or D.
Column G is the absolute value of column A minus column D.
Column H is the average i.e. column E from above.
Column I is the daily high (column B) minus column E.
Column J is column E minus the daily low (column C).

The chart goes together in three steps. The first step is to create the
Open-Close bars. Create a stacked column chart based on this data:

f g
Date Low High
1/1/2005 55 1
1/2/2005 55 1
1/3/2005 55 1
1/4/2005 56 1
1/5/2005 55 2
1/6/2005 54 1
1/7/2005 53 1
1/8/2005 54 2
1/9/2005 55 1

Double-click on the bottom set of columns and go to Format Data Series -
Patterns Tab - Border = None and Area = None. When complete, all you
should
see are the bars associated with the smaller values.

The second step is to create the average. Click onside of the chart, go
to
the standard toolbar, and click on Chart - Source Data - Add - Values.
Choose the average value range for this series (55.67's). Click OK and
you
should have some more columns on the chart. Click on the new columns, go
to
the standard toolbar, and go to chart - chart Type - line - OK. When
complete you should have the Open-Close bars with an average line in the
chart.

The third step is to add the High-Low lines. Double-click on the average
line and in the Format Data Series dialog box go to the Y-Error Bars Tab.
Click in the Custom option. For the positive error bars, choose the Daily
High range (column I). For the negative error bars, choose the Daily Low
range (column J).

All you have to do now is format the chart like you would like.

----
Regards,
John Mansfield
http://www.pdbook.com



"Bert" wrote:

Hello,

I've this columns:
-Date
-Open
-High
-Low
-Close
-Avg14

Now I'm lookin for creatin a chart that:
* uses the Date-field on the X-axis (no problem)
* Creates a candle-graph based on the fields Open, High, Low, Close (also
no
problem)
* AND: shows as line-graph based on the field Avg14

The last one is the problem. Does anyone have an idea?

tia,
Bert