Charts with additional "markers"

[John McGraw]

I am creating frequency charts in excel, and I would like to be able to add a marker (maybe a vertical line) to indicate where the mean and the median are in the distribution. I dont want to have to do it manually, as the charts are changing often.

Is there a way to automatically include a marker in a chart based on a data in a cell?

 

[Mark W.]

Yes. Shall I elaborate?

 

[Mark O'Brien]

On 2002-03-07 13:54, Mark W. wrote:
Yes. Shall I elaborate?

Glad to see I'm not the only one in a difficult mood today.

I'd like to see the answer as well because it's not something I've had to look for yet, but I may someday.

(Just had a quick look and I'm sensing "Add Trendline" in the near future.)

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-07 13:57
This message was edited by Mark O'Brien on 2002-03-07 14:01

 

[John McGraw]

On 2002-03-07 13:54, Mark W. wrote:
Yes. Shall I elaborate?

It would be nice if you could. But you dont have to.

 

[Mark W.]

Okie, dokie...

Suppose we have a range of values (named Sheet1!X) containing {2;3;3;3;1;1;2;1;3} which is summarized as {"Bin","Freq";1,3;2,2;3,4} (and whose columns are named Sheet1!Bin and Sheet1!Freq respectively). Before I start charting I'll create one more Defined Name, Sheet1!Mean, which refers to ={0,0}+AVERAGE(Sheet1!X).

On to charting...

1. Select an empty cell and choose the Insert | Chart... menu command to create a blank Column Chart.
2. With the blank chart object selected type =SERIES(,Sheet1!Bin,Sheet1!Freq,1) into the formula bar.
3. Once again with the chart object selected type =SERIES(,Sheet1!Mean,{0,5},2).
4. Select the 2nd series, choose the Chart | Chart Type... menu command, and pick the XY (Scatter) Chart type and one of the sub-types on the 3rd row.
5. With the chart object selected choose the Chart | Chart Options... menu command and uncheck the Secondary Axis for X and Y.

Voila!! A histogram with a vertical line marking the Mean.
This message was edited by Mark W. on 2002-03-07 14:42