Charts with additional "markers"

John McGraw · Mar 7th, 2002, 02:52 PM

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. · Mar 7th, 2002, 02:54 PM

Yes. Shall I elaborate?

Mark O'Brien · Mar 7th, 2002, 02:56 PM

Quote:

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

_________________
[b] 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 · Mar 7th, 2002, 03:17 PM

Quote:

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. · Mar 7th, 2002, 03:37 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 02:52 PM	#1
John McGraw Board Regular Join Date: Feb 2002 Posts: 76	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?

Mar 7th, 2002, 02:54 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Yes. Shall I elaborate?

Mar 7th, 2002, 03:37 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]